Skip to main content

How to Convert String to Array Using Formula in Excel

Convert String to Array

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


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


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:


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 ";":


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


Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>