Convert Text to Columns

If you work with a lot of data from different sources, there could be occasions where you receive a text file with values that are separated by commas. When you copy and paste the values in Excel, it would place them all in one column. You can separate these values into different columns using the text to columns command.

To convert delimited text values into separate columns, do the following:

1. Select the range that contains the text you want to split.
2. On the Data tab click on Text to Columns .
3. In the Convert Text to Columns Wizard, select Delimited and click on Next .
4. Select the Delimiters for your data. For our example (above), the delimiters are Comma and Space . You also have the options of Tab, Semicolon, and Other, which allows you to specify the delimiter if it’s not one of the default options.
   The Data preview portion of the screen shows you a preview of how your data would look after the conversion.
   
5. Click on Next .
6. At the next screen, select the Column data format or use what Excel chooses for you.
7. In the Destination field, click the up arrow, and on your worksheet, select the top leftmost cell where you want the split data to appear. The cell reference for the destination will be entered in the field.
   
8. Click on Finish .

The delimited text will now be split into different columns. You can delete the initial column with the original text from the worksheet or move it to another sheet if you want to keep it.