CODEWITHSUNDEEP
excelformula
YasserKhalil
YasserKhalil

Reputation: 9538

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

Upvotes: 0

Views: 51

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

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)

Upvotes: 1

Related Questions