Excel Annoyances

TEXT FORMULA ANNOYANCES

PARSE FIXED-LENGTH SUBSTRINGS

The Annoyance:

I work for a car dealership, so I work with a lot of Vehicle Identification Numbers (VINs). A VIN is 17 characters long and contains information about the car maker, the car model, the year it was made, and so on. The dealership’s VIN tracking software writes the VIN of every car we have on the lot to a text file. What I want to do is break down each car’s VIN (a representative sample is shown in Figure 3-22) into meaningful chunks. Can Excel do that?

Figure 3-25. You can use this data’s pattern to separate it into its component parts.

The Fix:

The trick is to import the data and use an Excel wizard to parse the string. The following works when each field in the string is of a known length:

1. Choose File → Open, click the down arrow at the right of the “Files of type” drop down, and select All Files (*.*). Select the file you want to import, and click the Open button. This starts the Text Import Wizard.

2. Select the Fixed Width option and click the Next button.

3. On the second page of the wizard, click the ruler above the data preview area to set where each break line goes. Figure 3-23 shows an import with four break lines—the string will be broken into five chunks.

   

   Figure 3-26. The lines mark the breaks between substrings in data ...