XL: How to Parse Data Using the Text To Columns Command

ID: Q142149

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition
Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for the Power Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows NT, version 5.0

SUMMARY

This article contains information about parsing 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, several columns of data may be condensed 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 be many rows, but only one column of data can be converted at a time.

To create an example that demonstrates how to use the Text To Columns command, use the following steps:

1. Create a new workbook.

2. In Sheet1, enter the following data:

      A1: The,dog,ran
      A2: 156,156,451
      A3: abc,def
      A4: abcdefg,hijkl
      A5: 2554,4874,41

3. Select the range A1:A5.

4. On the Data menu, click Text To Columns.

5. In Step 1 of the Convert Text To Columns Wizard, make sure the

   Delimited option is selected, and then click Next.

6. In Step 2 of the Convert Text To Columns Wizard, select the Comma

   check box under Delimiters, and click the Next button.

   NOTE: All of the check boxes Under Delimiters should be clear 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: 156,156,451     B2: 156     C2: 156   D2: 451
   A3: abc,def         B3: abc     C3: def
   A4: abcdefg,hijkl   B4: abcdefg C4: hijk
   A5: 2554,4874,41    B5: 2554    C5: 4874  D5: 41

REFERENCES

Microsoft Excel 7.0

For more information about parsing data in Microsoft Excel, click Answer Wizard on the Help menu and type:

   tell me how to parse data

Microsoft Excel 5.0

For more information about Parsing Data in Microsoft Excel, click the Search button in Help and type:

   parsing

Additional query words: 5.00 5.00a 5.00c 7.00 7.00a 97 98 XL98 XL97 XL7 XL5

Keywords          : xlformat 
Version           : WINDOWS:5.0,5.00c,7.00,7.00a,97; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto kbinfo

Last Reviewed: January 7, 1999