Search code examples
excelformula

Split cells contents separated by comma using formulas

I am using Excel 365 and there are items in column A and some cells have a comma as separtor like that

Column A
--------
Ahmed
Salem, Ahmed
Reda
Khaled
Salah, Ahmed

I have used this formula to split the contents

=TRANSPOSE(FILTERXML("<AllText><Num>"&SUBSTITUTE(A2,",","</Num><Num>")&"</Num></AllText>","//Num"))

But the results I got are in multiple columns. How can I get all the results in just one column? The expected result would be in one column

Ahmed
Salem
Ahmed
Reda
Khaled
Salah
Ahmed
Solution

  • You just need to join all the cells into a single string, before creating the XML:

    =FILTERXML("<AllText><Num>"&SUBSTITUTE(TEXTJOIN(",",TRUE,$A:$A),",","</Num><Num>")&"</Num></AllText>","//Num")
    • Note TEXTJOIN(",",TRUE,$A:$A)