< Back

Excel: Text to columns: extract data and add delimiters

Sun Sep 09, 2018 7:48 am

You may have some data in text format that you need to separate into columns . Excel's Text to columns feature helps you with this.



You can find this option under Data->Text to Columns



Image



Here are the steps to split data using Text to Columns
  • Select the cell or column that contains the text you want to split.
  • Select Data > Text to Columns.
  • In the Convert Text to Columns Wizard, select Delimited > Next.
  • Select the Delimiters for your data. For example, Comma and Space. You can see a preview of your data in the Data preview window.



    Image
  • Select Next.
  • Select the Column data format or use what Excel chose for you.



    Image
  • Select the Destination, which is where you want the split data to appear on your worksheet.
  • Select Finish.
How to add delimiters

  • Sometimes the data that you have will not have delimiters, in this example consider a data set with "Phone 123456789" in text. If you use SPACE as delimiter then that may affect formatting of other data.
  • We can use Find & Replace to add delimiters to text that we need.
  • Replace Phone with Phone$ or use any delimiter you like.



    Image
  • After replacing the text you can run Text to Columns to split/extract the data that you need.



    Image
You can use this to add delimiters and extract any specific data that you need from your source text.