excel extract each word from a cell and store into separate columns

user_11135484 0
Viewed 164

I have a cell which contains data like: 45677|abc|top|IG|pro|sjkdjs|cdf

and i want to extract each one of them into separate columns, is there an excel function that can do this or ill have to use pandas.

excel-formula excel
2 Answers

Use Data > Text to Columns and set the delimiter to |.

  1. Select the cell or column that contains the text you want to split.
  2. Select Data > Text to Columns.
  3. In the Convert Text to Columns Wizard, select Delimited > Next.
  4. Set the Delimiter to |.
    You can see a preview of your data in the Data preview window.
  5. Select Next.
  6. Select the Destination in your worksheet which is where you want the split data to appear.
  7. Select Finish.
user_150513100

For one row you write how you want it to be. enter image description here

just like given in above picture.

And then

  1. Select the cell B1
  2. Press CNTRL + E

Follow above 2 steps for each of cell i.e. C1,D1,E1,F1,G1 and H1 and you'll get all the automatically filled with delimiter. Result would be like below img: enter image description here

user_34337020
Related