Clever Geek Handbook

Split row (cell) in Excel without VBA (like for array formula)

I have data in an Excel spreadsheet with separator rows. For simplicity, think of something like "4#8#10"

, with an arbitrary number of pieces.

Is there a way to split this into an array for use with an array formula? For example, something like SUM(SplitStr(A1,"#"))

. (This doesn't work - it just returns "4"

.)

Is there a good way to approach this? My ideal solution would be to get the array as if the user had entered {4,8,10}

for use in array formulas.

Applying my sheet does not allow separator based columns and summarize them. I would rather not use VBA if possible, as I will not be the main consumer of the sheet.

Thanks Terry

+3


source to share


3 answers


To summarize 4,8,10 entries, you can use something like:

=SUMPRODUCT(1*TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)))=1),99)))

The returned array is a text array, so the 1 * at the beginning is one way to convert them to numbers

This part:

TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)))=1),99))


returns an array:

{"4";"8";"10"}

And from 1 * to it:

{4;8;10}
+8


source


With your data in A1, in B1 enter:

=TRIM(MID(SUBSTITUTE($A1,"#",REPT(" ",999)),COLUMNS($A:A)*999-998,999))


and copy it.

+2


source


Use XML functionality:

={SUM(FILTERXML("<t><s>" & SUBSTITUTE(A1, "#", "</s><s>") & "</s></t>", "//s"))}
+2


source






More articles:


All Articles