Excel: Text to columns: extract data and add delimiters

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







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.
  
  
  
  
• Select Next.
• Select the Column data format or use what Excel chose for you.
  
  
  
  
• 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.
  
  
  
  
• After replacing the text you can run Text to Columns to split/extract the data that you need.
  
  
  
  

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