Parse Data using Text to Columns

December 15, 2021 - by Bill Jelen

Problem: A vendor gave me a file that contains three-segment item numbers. The segments are separated by dashes. The FIND function makes my head hurt, but I need to break the part number into three columns. What do I do?

Column A contains item numbers that contain three sections separated by a hyphen. — Figure 225. Split the item number at each dash.

Strategy: You can use the Text to Columns command on the Data tab 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 F. Place the cell pointer in cell F2. Press Ctrl+Shift+Down Arrow.
3. Select Data, Text to Columns. The Convert Text to Columns Wizard will work on either data that is delimited or on data that has a fixed width to each segment.
4. Because the data in this example is delimited by a dash, in step 1 of the wizard, leave the radio button on the Delimited setting.
5. Click Next.

Step 1 of the Text to Columns wizard is where you choose either Delimited or Fixed Width. — Figure 226. The dashes are known as delimiters.

6. By default, step 2 of the wizard assumes that the data is delimited by a tab, so uncheck the Tab check box. Other standard choices are commas, spaces, and semicolons. Since dash is not in the list, you should choose the Other check box. In the Other text box, enter a dash. The Data Preview window will show the data in three columns.
7. Click Next.

In step 2 of the wizard, choose a delimiter. There are checkboxes for common delimiters of Tab, Semicolon, Comma, and Space. But your delimiter is a hyphen. Choose the checkbox for Other and then type a hyphen in the Other box. — Figure 227. Specify a dash in the Other box.

8. Click Next. In step 3 of the wizard, if desired, specify the data type of the columns. Unless you have dates, the General type is okay. Note that if you want to preserve any leading zeros in the second segment of the item number, you should choose the heading of that field and change it from General to Text.

In Step 3 of the wizard, you can choose each column of the result and select a data format of General, Text, Date (in several formats) or select Do Not Import Column (skip). In this figure, the numeric second segment of the part number is selected and you've chosen Text in order to keep the leading zeroes. — Figure 228. Use Text only when you have to preserve leading zeros.

9. Click Finish.

Results: The original column F has been overwritten with the first portion of the result. Columns G and H contain the second and third segments of the item number.

The three segments now appear in columns F, G, and H. — Figure 229. You’ve parsed column A into three new columns.

Gotcha: The General format will aggressively attempt to convert anything that is remotely similar to a date to a date. For example, a part number of 5-5055 will be imported as May 1, 5055. A fraction such as 1/4 will be imported as January 4 of the current year. If your data includes dashes or slashes, use the Text format.

Gotcha: Avoid using the Text option in step 3 of the wizard unless it is absolutely necessary. In addition to preserving leading zeros, the Text option will change the format of that column to text. When you try to enter a formula in that column, you will get the formula instead of the answer. To solve this problem, you have to select the column, press Ctrl+1 to format cells, and select Number. Then you select any numeric format. You then have to go back and reenter the formulas in order to have them calculate.

This article is an excerpt from Power Excel With MrExcel

Title photo by Jeswin Thomas on Unsplash