Data in one cell in excel wants to be divided in multiple cells

Question

ABC|DEF|GHI|JKL|

I have the above data. I want to create a formula by which all the data will get divided into different cells using | as delimiter

Answer 1

FILTERXML() will give you desired result.

 =FILTERXML("<t><s>"&SUBSTITUTE(A1,"|","</s><s>")&"</s></t>","//s")

To get results in horizontal alignment use TRANSPOSE()

=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1,"|","</s><s>")&"</s></t>","//s"))

More about FILTERXML() is here by JvdV Extract substring(s) from string using FILTERXML

Answer 2

You can try Text to Column

1. "Data" Tab
2. "Text to Columns"
3. "Delimited" Option
4. Type the bar "|" in the "Other" option box