My method is to parse in place - very similar to saving as a text file and re-importing, but you can do this without closing and re-opening the file. This is probably a good assumption because the problem of 'bad' blanks is caused by the confusion between blanks and nulls - usually in the data imported from some other place - so there shouldn't be any formulas. It assumes that your data has only values without formulas. My method is similar to Curt's suggestion above about saving it as a tab-delimited file and re-importing. Find('', LookIn:=xlValues, LookAt:=xlWhole) Find(Chr(10), LookIn:=xlValues, LookAt:=xlWhole)Īfter file supplied: Select the range of interest for improved performance, then run the following: Sub find_newlines() ' ' & Char(10) is a space and a newline: Sub find_newlines() Remember the Chr(10) is a newline only replace this as required, e.g. Right click on the sheet tab > view code.If you have newlines this is more difficult and requires VBA: Enter one space in the find what, leave replace with empty and ensure you have match entire cell contents is ticked in the options.

Press ctrl + h to open find and replace.

If you only have a space in the cells these can be removed easily using:

If you select the cell and click in the formula bar and use the cursor to select all.

This number is the ASCII number used in =CHAR(32).

The =CODE(A1) formula will return a #VALUE! if the cell is truly empty, otherwise a number will return.

To find these cells quickly you can do a few things. A revelation: Some blank cells are not actually blank! As I will show cells can have spaces, newlines and true empty:

Cant select cell office mac 2017