I was recently asked about an issue where a cell appears empty but for some reason Excel still thinks there is something in that cell.
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:
It 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 how do we fix it?
Getting rid of the fake blanks
There are a few ways to do this, so let’s look at three of them.
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: