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?
- 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:
- 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.
- Select the entire range of data in column F. Place the cell pointer in cell F2. Press Ctrl+Shift+Down Arrow.
- 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.
- 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.
- Click Next.
- The dashes are known as delimiters.
- 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.
- Click Next.
- Specify a dash in the Other box.
- 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.
- Use Text only when you have to preserve leading zeros.
- 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.
- 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.