CODEWITHSUNDEEP
excelexcel-formula
user334028
user334028

Reputation: 7

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

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

Upvotes: 0

Views: 198

Answers (2)

Abdullah Bayounis
Abdullah Bayounis

Reputation: 265

You can try Text to Column enter image description here

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

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 37145

FILTERXML() will give you desired result.

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

enter image description here

To get results in horizontal alignment use TRANSPOSE()

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

enter image description here

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

Upvotes: 2

Related Questions