Un-Delimiting

[caveman.savant]

If I find that I have a single cell with values delimited with | , how
could I split each value into new cells across columns

so

YES|NO|MAYBE in A1 would be "YES" in C1, "NO" in D1, "MAYBE" in E1
NO in A2 would be "NO" in C2
YES|NO|MAYBE|ALWAYS in A3 would be "YES" in C3, "NO" in D3, "MAYBE" in
E3

 

[Mike H]

Hi,

Select the data then

data|Text to columns
Select delimited
Next
Check 'other' and enter your delimiter in the other box
Finish

Mike

 

[caveman.savant]

Great Solution!
Do you think there would be a way to do that within a formula?
like
=LEN($A$1)-LEN(SUBSTITUTE($A$1,"|",""))+1
to find the number of time "|" occurs
and then do a midstring in C1 based on A1 to get everything up to the
"|"
and then do a midstring in C2 based on A1 to get everything up to the
(2nd) next "|"
....and so on until the number of "|" occurances is met