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?

Data from this book's Table of Contents is pasted into Excel. In column A, you have the topic title, then a bunch of periods between the title and the page number. There will be more or less periods depending on how long the title is. Right now, using a modern font, the page numbers do not appear to be lined up. — Figure 233. 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.

Change the Table of Contents entries to Courier New font. Now you can see that the page numbers are perfectly lined up. In Courier New, each character takes the same amount of space. — Figure 234. 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.

In Step 1 of Text to Columns Wizard, choose Fixed Width — Figure 235. Choose Fixed Width in step 1.

In Step 2, carefully click after the last period and before the page number in order to add a line. It is not shown in this example, but this will fail later when the page numbers change from 99 to 100. — Figure 236. 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.

Column A still contains a bunch of periods. In Find and Replace, change two periods to nothing. Click Replace All several times until Excel reports that nothing was changed. — Figure 237. 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.

Rather than the convoluted steps shown above, choose Delimited as the type, a Period as the Delimiter, and check the box for Treat Consecutive Delimiters as One. This awesome trick will treat the .......... as a single delimiter. — Figure 238. Treat adjacent periods as one.

This article is an excerpt from Power Excel With MrExcel

Title photo by Marek Piwnicki on Unsplash