How to Convert String to Array Using Formula in Excel

Convert String to Array

In cell A1 there is a string separated by ",":

A,1,b,2,Dx

Now, how can I convert A1 to an array using a formula?

{"A";1;"b";2;"Dx"}

IF you use MID function:

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)))-1)*LEN(A1)+1,LEN(A1)))

But, if you use the FILTERXML function, it's very simple:

=FILTERXML("<r><c>"&SUBSTITUTE(A1,",","</c><c>")&"</c></r>","//c")

In this formula, you can replace "r" or "c" with your favorite string.

You can also use the formula to sum the string. E.g. Cell A1 contain a string separated by ";":

1;2;3;4;5

=SUM(--(MID(SUBSTITUTE(A1,";",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+1)))-1)*LEN(A1)+1,LEN(A1))))

=SUM(FILTERXML("<r><c>"&SUBSTITUTE(A1,";","</c><c>")&"</c></r>","//c"))