Microsoft Excel: How To Separate A Part Number Into Three Columns

Problem: A vendor file contains a three-segment item number, as shown in Fig. 280. Each segment is separated by a dash. The FIND function makes your head hurt. Break the Part Number into three columns

Strategy: Use the Text to Columns command on the Data menu to parse the item number. Follow these steps.

1) Copy the item number to the right side of your data in column F. The Text To Columns command will fill several columns to the right of the original column. Make sure you have plenty of blank columns.

2) Select the entire range of data in column G. Place the cell pointer in G2. Hit the End key. While holding down the Shift key, hit the Down Arrow key to select the entire range.

3) From the menu, select Data – Text to Columns. The Wizard will work on either data that is delimited or on data that has a fixed width to each segment. Our data is delimited by a dash. As shown in Fig. 281, in Step 1, leave the radio button on the Delimited setting. Click Next.

By default, Step 2 assumes that the data is delimited by a tab. As shown in Fig. 282, other choices are commas, spaces, and semicolons. Note that dash is not in the list.

4) Uncheck the Tab checkbox. Check the Other checkbox. In the Other textbox, enter a dash. As shown in Fig. 283, the data preview window will show the data in three columns. Click Next.

5) In Step 3, you can optionally specify the data type of the columns. Unless you have dates, the General type is OK. Note that if you want to preserve the leading zeroes in the second segment of the item number, you should choose the heading of that field and change from General to Text, as shown in Fig. 284. Click Finish.

Result: The original column F has been overwritten with the first portion of the result. New columns G and H contain the second and third segments of the item number, as shown in Fig. 285.

Gotcha: A very strange anomaly will appear for the remainder of this Excel session. If you later open a Notepad file, copy data that contains dashes, and attempt to paste to Excel, Excel will automatically split the data into columns where the dash is located, as shown in Fig. 286 and Fig. 287. This can be a very handy feature if you are expecting it, or a very puzzling situation if you are not.

Summary: If your data is set up with consistent delimiters, the Text to Columns Wizard is a fast way to parse data.