Parse Data With Leader Lines
December 23, 2021 - by Bill Jelen

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

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.

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.


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.

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.

This article is an excerpt from Power Excel With MrExcel
Title photo by Marek Piwnicki on Unsplash