Excel: Filter by Text Length to Delete Empty Rows that aren’t Blanks

Go on, delete a gazillion useless rows for me!

If you want to delete blank rows from large Excel files, but Excel’s Data | Auto Filter won’t identify empty-looking rows as Blank, then this short post is for you.

Even if you’re familiar with one or more of the various ways to eliminate blank rows, you may get stuck.

What looks like an empty row in Excel may not be “blank.”

• If the file is a CSV, then an empty row may consistent of commas only, or perhaps a newline character only. Open the CSV file in a text editor to check.
• The row may contain one or more unprintable characters. (Refrain, if possible, from saying unprintable things about the file.)
• The file may have something else wacky going on.

If the Excel file has formatting such as colors, gridlines, or the like, and if you don’t have formulas you need to save, try saving the Excel file as a CSV file. Then review the raw text in a text editor.

In Excel, try to eliminate empty-looking rows using a formula that finds text length. For your data columns you’ll concatenate (combine) the text and then calculate the length of that text.

=LEN(CONCAT([column1,column2,…,columnN]))

1. In any row, select an empty cell to the right of your columns with data.
2. In the cell, start typing =LEN(CONCAT(
3. As the input to CONCAT(…), click and drag to select data cells in that row.
4. Press Enter to see the length of the concatenated (combined) text.
5. Copy & paste the cell with your formula to the remaining cells in the column.
6. Check whether rows with empty-looking cells have a have a text length of 0 (zero).
7. Highlighting all columns: your data columns and the column with your text length calculation.
8. Select Data menu, then AutoFilter
9. Filter rows for which text length is 0.
10. Observe that the filtered display shows only empty rows
11. Right click on any row number.
12. In the right click popup menu, click Delete Rows
13. In the Data menu, uncheck AutoFilter
14. Check whether empty rows have been deleted as expected.

Best of luck!