Excel: Parse Data With Leader Lines

Problem: Someone sent me data with leader lines (........) between the columns. How can I parse the data?

1. Break the data at the leader lines.

   Strategy: First, see if the data is fixed width by changing the font to Courier New or Courier. These fonts are fixed width fonts. If the second field lines up in Courier font, then you know that you can use the Fixed Width version of Text to Columns.

   

2. In Courier New font, this data lines up.

   Follow these steps:

   1. Select the data in column A. Use Data, Text to Columns.
   2. In Step 1, choose Fixed Width. Click Next.
   3. In step 2, click in the data preview area where the second field begins.

   

3. Choose Fixed Width in step 1.

   

4. Click to add a vertical line.
   4. Click Finish.

   The resulting data will still have the leader lines in column A. Use Home, Find & Select, Replace. In the Find What box, type two periods. Leave the Replace With box blank. Click Replace All. Click OK. Click Replace All again. Click OK. Finally, replace a single period with nothing. Click Replace All.

   

5. Use Find and Replace to get rid of leader lines.

   Alternate Strategy: If there are no periods other than the leader lines, you could do a delimited Text to Columns. In Step 1, choose Delimited. In Step 2, choose Other and enter a period as the other delimiter. The important difference is to choose Treat Consecutive Delimiters as One. Click Finish.

   Gotcha: This method will fail if any entries in the first field contain a period. After completing the text to columns, go to the presumably blank third column, select a cell, and press Ctrl+Down Arrow. If you run into any data, fix that row manually. If you end up at row 1048576, then you know the column is blank as expected.

   

6. Treat adjacent periods as one.