# 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"}`

### MID function

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)))``

### FILTERXML function

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.

## SUM the 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`

### MID function

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

### FILTERXML function

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