How to parse data using the Text To Columns command in Excel

Article translations Article translations
Article ID: 214261 - View products that this article applies to.
This article was previously published under Q214261
Expand all | Collapse all

SUMMARY

This article contains information about how to parse data in Microsoft Excel by using the Text To Columns command.

MORE INFORMATION

If you copy data from another program and paste it into Microsoft Excel, Excel may condense several columns of data to a single column. You can use the Text To Columns command on the Data menu to put each record in a separate column. The range that you convert may contain many rows, but you can only convert one column of data at a time.

To create an example that demonstrates how to use the Text To Columns command:
  1. Create a new workbook.
  2. In Sheet1, type the following data:
          A1: The,dog,ran
          A2: abc,def
          A3: abcdefg,hijkl
          A4: 2554,4874,41
    					
  3. Select the range A1:A4.
  4. On the Data menu, click Text to Columns.
  5. In Step 1 of the Convert Text To Columns Wizard, make sure that the Delimited option is selected, and then click Next.
  6. In Step 2 of the Convert Text To Columns Wizard, click to select the Comma check box under Delimiters, and then click Next.

    NOTE: All of the check boxes under Delimiters should be cleared except for the Comma check box.
  7. In Step 3 of the Convert Text To Columns Wizard, type B1 in the Destination box, and then click Finish.
Your data is parsed into separate columns, beginning with column B, and the data in Sheet1 should resemble the following:
   A1: The,dog,ran     B1: The     C1: dog   D1: ran
   A2: abc,def         B2: abc     C2: def
   A3: abcdefg,hijkl   B3: abcdefg C3: hijkll
   A4: 2554,4874,41    B4: 2554    C4: 4874  D5: 41
				
Note The example illustrates data with commas as the delimiter. This is only one of several delimiters that you may see in your data. For example, if you have Names in a column of data, consisting of first and last name in a single cell separated by a Space, the Space character can be used as a delimiter to separate the first and last names into adjacent cells instead of the same cell.

REFERENCES

For more information about parsing data, click Microsoft Excel Help on the Help menu, type parse data in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Properties

Article ID: 214261 - Last Review: September 18, 2011 - Revision: 6.0
APPLIES TO
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel X for Mac
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 2008 for Mac
Keywords: 
dftsdahomeportal kbhowto KB214261

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com