CODEWITHSUNDEEP
excelexcel-formula
Officer Bacon
Officer Bacon

Reputation: 744

Get multiple substrings from cell by separator

I have multiple values in one cell that are ordered like this:

0#0#54232#5#123# ...

Now I want to put every single value in a separate cell. I tried working with SEARCH, FIND, LEFT, MID functions and so on, but everthing looks so fiddly and I can't get it to work. My idea was to look for every # in the text and get every substring in between. But with 20+ values in a single cell this gets very confusing by the end.

EDIT: The lengths of the values can vary

Upvotes: 0

Views: 1061

Answers (3)

basic
basic

Reputation: 11968

You can use following formula (also in older excel versions):

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE($A$1;"#";"</s><s>")&"</s></t>";"//s["&ROW()&"]");"")

enter image description here

Upvotes: 2

Michael Wycisk
Michael Wycisk

Reputation: 1695

You can use FILTERXML to split the string (this solution will work on Office 365, since dynamic arrays need to be available):

=FILTERXML("<t><s>"&SUBSTITUTE(A1;"#";"</s><s>")&"</s></t>";"//s[string-length()>0]")

Split string using FILTERXML in Excel

There has recently been a nice question here on Stackoverflow that I highly recommend to read:

Excel - Extract substring(s) from string using FILTERXML

Upvotes: 2

Dominique
Dominique

Reputation: 17565

I've put your string in cell B3, and in cell C2, I've put following formula:

=LEFT(B3;FIND.SPEC("#";B3)-1)

In cell C3, I've put following formula:

=SUBSTITUTE(B3;C2&"#";"";1)

I've been dragging the whole thing to the right, and row 2 gave me the results you are looking for. (You might need to add some IfBlank() or IfError() but you understand what I mean)

Upvotes: 1

Related Questions