I was recently asked about an issue where a cell would appear empty, but for some reason Excel still thinks there is something in that cell.
This is most likely because the cell contains a zero-length string. This can happen when the cell only contains an apostrophe ( ‘ ) or two double quotation marks (e.g. in the formula in cell C3 in the image below).
This will mess with formulas, e.g. in the “Paste values” table below the COUNTA function in cell C14 counts five items in the range C9:C13, instead of just three, as the zero-length strings are copied down to cells C10 and C11:
These fake blanks can also cause data to display incorrectly. Because there is already “something” in the cell it will cover text running over into it from an adjoining cell:
As you can see in the image above, in the second and third row the text is cut off because of a seemingly empty cell having this mystery “something” in it.
So now you know WHY it happens, but more importantly… how do we fix it?
Getting rid of the zero-length strings (i.e. fake blanks)
There are a few ways to clear these fake blanks in Excel, so let’s look at three of them. Download the workbook that you can use to test it out)
1) Just delete them
If you know exactly where these cells are, and if there are just a handful, the quickest and easiest way to fix it is by deleting it. Select the cell and press Delete, or press F2 to edit the cell and then just press Enter.
2) Use text-to-columns
This is a good method for when you have too many cells to delete individually, but they are all in the same column.
Step 1. Select the column with the offending “blanks”.
Note: Make sure at least one of the selected cells has a value in it. If all the cells are either blanks or fake blanks, Text to Columns thinks there’s nothing to do and it doesn’t work. So in the example above I’ve added an “a” at the top to make sure the Text to Columns actually works. Just remember to delete it when you’re done. But… you shouldn’t ever need this workaround because if all the cells are either blank or fake blank you just select the whole column and press Delete as explained in (1) above!
Step 2. Select “Text to Columns” in the Data tab of the ribbon:
And in the next screen, don’t do anything, just click “Finish”.
Done! All the fake blanks are gone.
3) Find and Replace
If the worksheet is riddled with fake blanks everywhere and you are worried about it potentially causing issues down the line, you can get rid of them all at once.
Step 1. Press Ctrl+H to open the Replace dialog (or you can find it on the HOME tab of the ribbon, usually at the far right:
- Leave the “Find what” field blank.
- Type something random in the “Replace with” field. Make 100% sure it is something that doesn’t already exist somewhere in the worksheet! I typed “qqq”:
- Press Alt-A or click “Replace All” – now all of the blank cells will be replaced by “qqq”:
- I know it looks freaky, but stay calm… it will all be fixed soon! As you can see the “fake blank” in row 29 was replaced as well, so we’re on the right track.
Step 2. The last step is to reverse the process.
- Press Ctrl+H to open the Replace dialog again.
- This time type your random string in the “Find what” field and leave the “Replace with” field blank.
- Press Ctrl-A to again replace all…. and voila! All fixed:
In the image above we can see the COUNTA function has counted only three items, and not four anymore, as the “fake blank” cell is now really blank. This is the most efficient way of fixing blank cells that aren’t actually blank.
Download the workbook and try it out!