Data copied from a web site or imported from a variety of sources often include irrelevant rows of data.
For example, the only way to get data from some sources is to print the data as a file — complete with report headers, footers, subtotals, and so on — then open the file in Excel. You then have a challenge of deleting the unneeded rows of data.
In How to delete blank or unneeded rows in your worksheet, Method 1, I described how to delete only rows of blanks within the imported data. This post explains how to delete unneeded rows with both blanks and “junk data”.
Use Text-to-Columns If Necessary
If the data comes into Excel as text in column A, your first step is to split it into columns. To do so, select column A, and then…
- In New Excel, choose Data, Data Tools, Text to Columns
- In Classic Excel, choose Data, Text to Columns
These steps launch the Convert Text to Columns Wizard, which you can follow to split the text in column A into columns of text and data.
Add a Counter Column
You now have rows of data interspersed with rows of junk. The easiest way to group the rows of junk together so that you can delete them is to sort the data by one of its columns.
However, after you remove the junk you probably will want your data to be returned to its original sequence. Therefore, you first need to add a counter column that allows you to sort the data back into place.
Select all of column A by clicking in its column header. Press Ctrl++ (Ctrl plus the + key) to insert a new column A. Enter the value 1 in the first row of column A that’s next to your first row of data. Now select all of column A within the used range. To do so, hold press Ctrl+Shift+End. Then press Shift+Home.
You now need to fill the selected area with a sequence of numbers. To do so…
- In New Excel, choose Home, Editing, Fill, Series
- In Classic Excel, choose Edit, Fill, Series
In the Series dialog, Excel should already have entered 1 as the step value. So just choose OK.
Sort Your Data
With Method 1, you picked a control column that had blanks that you could safely delete. With this method, you pick a control column to sort. The column should be one that contains all the data you need. However, it also could have junk data, including empty cells.
Sort by this column to group all the similar types of data together. After you do so, visually inspect the results to find rows with junk data above and below your good data. Delete the rows of junk data.
After you delete the junk data, sort it back into its original sequence by sortin your good data by the counter values in column A.
Occasionally, some junk data will remain. When this happens, you might need to choose another control column to sort on