0

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

Dominique
  • 8,687
  • 9
  • 28
  • 67
Officer Bacon
  • 564
  • 4
  • 20

3 Answers3

2

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

Michael Wycisk
  • 1,356
  • 5
  • 17
  • 1
    Nice. If there can be empty nodes, you can exclude them through `//s[node()]` also. If the string wont end by `#` it's simply `//s`. Maybe also worth noting that without Excel O365 one should use `INDEX` to pull values from the resulting array. + – JvdV Aug 05 '20 at 09:54
2

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

basic
  • 8,639
  • 2
  • 5
  • 23
1

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)

Dominique
  • 8,687
  • 9
  • 28
  • 67