If a cell appears to be empty but the ISBLANK function returns false, it is for one of 3 reasons:

1. The cell contains a regular space.

2. The cell contains a non-breaking space.

3. The cell contains a zero-length string.

Regular spaces can be removed by using the TRIM function and non-breaking spaces can be removed by using a formula. If the cell contains a regular space or a non-breaking space the LEN function will return a number greater than 0.

In Microsoft Excel both blank and empty cells display as empty, however empty cells do not contain a formula or value, whilst blank cells contain a formula or value that evaluates to or represents a zero-length string. A zero-length string, also known as an empty string is a string that is zero characters long, as a result the LEN function returns 0 when a cell contains a zero-length string.

There are 3 types of zero-length strings in Excel:

1. Zero-length strings output by a formula: a zero-length string can be entered into Excel by typing two consecutive double quotation marks e.g. =““.

2. Zero-length strings formed from typing a single apostrophe into a cell: when an apostrophe ' appears as the first character in a cell, it is used to set the cell format to text and inserts a zero-length string into the cell.

3. Constant zero-length strings: these can result from a copy and paste as values operation of one of the two types of zero-length strings mentioned above. Constant zero-length strings can also be imported into Excel from a database that contains zero-length strings. N.B. If a cell contains a constant zero-length string the formula bar will be empty even though the cell is not empty.

Excel formula to determine if a cell contains a zero-length string

You can use the following formula to identify cells which contain zero-length strings:

=IF(AND(LEN(A2)=0,NOT(ISBLANK(A2))),"Zero-length string","Not a zero-length string")

A breakdown of the formula which checks to see if a cell contains a zero-length string

In our formula, the IF function checks to see if the length of cell A2 is 0 AND that cell A2 is not empty (this is represented by NOT(ISBLANK(A2)) in our formula), when these two criteria are met the formula returns ‘zero-length string’, when the criteria are not met the formula returns ‘Not a zero-length string’. N.B. The ISBLANK function will only return true if the cell is empty, if the cell is blank the ISBLANK function will return false (a more accurate name for the ISBLANK function would be ISEMPTY!).

The Go To Special dialog box can be used to find empty cells

The ‘Blanks’ option of the Go To Special dialog box will highlight empty cells in your data set (It should really be called Empty!).

You can open up the Go To Dialog Box by going to the Home tab and clicking on ‘Find & Select’ and then selecting ‘Go To’ from the dropdown options. Alternatively you can press F5 or Ctrl + G (also Ctrl + G on a Mac) to generate the Go To Dialog Box.

Once the Go To Dialog Box opens up, click the ‘Special’ button to generate the Go To Special dialog box.

Click on the ‘Blanks’ radio button.

Excel will select all of the empty cells in your data set.

4 ways to remove zero-length strings from your Excel worksheet

1. Use Find and Replace

You can use the Find and Replace tool to remove zero-length strings from your spreadsheet. To open the Find and Replace box you can press Ctrl + H (or Cmd + Shift + H on a mac). Alternatively you can go to the Editing section of the Home Tab and click the Find & Select icon.

Select ‘Replace’ from the dropdown list.

This will open up the Find and Replace dialog Box. Leave the ‘Find what’ field blank and enter ‘z’ in the ‘Replace with’ field. Tick the ‘Match entire cell contents’ box and then click the ‘Replace All’ button.

Switch the text in each field. Add ‘z’ to the ‘Find what’ box and remove the ‘z’ from the ‘Replace with’ box and click the ‘Replace All’ button.

All cells containing zero-length strings will now be empty.

N.B. If you leave the ‘Find what’ field blank it looks for both empty and blank cells, this is because the Find and Replace tool is unable to distinguish between empty and blank cells. We add ‘z’ to the Replace with field so that all cells containing zero-length strings will be replaced with a z (along with blank cells). Once we remove the letter z using ‘Replace with’ the cell becomes empty. It’s Excel’s default option to update all occurrences of the find string and not occurrences where the entire cell contents matches the find string. Hence why it’s very important to tick ‘Match entire cell contents’ because this ensures Excel only replaces cells which contain just the letter z. It will not update cells where the letter z is part of a text string e.g. cells which contain the words zoo or pizza will not be updated.

2. Use the ‘Clear All’ or ‘Clear Contents’ Buttons or press the ‘Delete’ key

You can remove zero-length strings from a cell by clicking the ‘Clear All’ or ‘Clear Contents’ buttons. Alternatively, you can press the ‘Delete’ key which invokes the clear contents command.

To get to the ‘Clear All’ and ‘Clear Contents’ buttons go to the ‘Editing’ section of the Home Tab and click on the Clear button.

Select ‘Clear All’ or ‘Clear Contents’ from the dropdown menu.

Another way to clear contents is to right click on a cell or a group of cells and select ‘Clear Contents’ from the dropdown menu.

3. Use Text to Columns

You can use the Text to Columns Wizard to remove zero-length strings from a column. Select the column that contains the zero-length strings you want to remove. Go to the Data Tools section of the Data tab and click on Text to Columns.

Click the Next button.

And click the Next button again.

Then click Finish.

The zero-length strings will now be removed from the column.

4. Use VBA Code

Lastly, you can use VBA code to remove zero-length strings from your worksheet. The VBA code below removes all zero-length strings from the used range of the active worksheet.

How Excel’s ISBLANK, COUNTA and COUNTBLANK functions treat empty and blank cells

The ISBLANK Function

The ISBLANK function returns TRUE if the cell is empty and FALSE if a cell is blank.

The COUNTA Function

The COUNTA function counts blank cells but does not count empty cells.

The COUNTBLANK Function

The COUNTBLANK function counts both blank cells and empty cells.