CODEWITHSUNDEEP
excel
Jay
Jay

Reputation: 17

Convert a cell containing a list of words into a column

'Bad','good','beautiful,pretty'

I want to convert the above into a row with each string in it.But when I use delimiters ' and , and then transpose , beautiful and pretty gets separated.But I wanted them to be in the same row. Like the following

Bad
good
beautiful,pretty

Is there a way for this?

Upvotes: 0

Views: 626

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

If you are going to use Text to columns you will split the result into separate columns (not rows). You could then copy/paste special transpose to get them into rows.

The most likely reason for you finding beautiful,pretty in separate cells is that you specified the double quote and not the single quote as the text qualifier.

If your version of Excel has the FILTERXML function, you can use, with your string in E1

=FILTERXML("<t><s>" &SUBSTITUTE(E1,"'", "</s><s>")&"</s></t>","//s[.!=',' and string-length()>0]")

enter image description here

Upvotes: 1

Related Questions