This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Parse Data With Leader Lines
Problem: Someone sent me data with leader lines (........) between the columns. How can I parse the data?
Figure 247 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.
Figure 248 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.
Figure 249 Choose Fixed Width in step 1.
3. In step 2, click in the data preview area where the second field begins.
Figure 250 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.
Figure 251 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.
Figure 252 Treat adjacent periods as one.
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.