Space : image credit - unsplash.com/NASAI have many clients who have the same problem with importing data from other applications into Excel. All seems perfectly well until they start to analyse the data only to find that it is not absolutely correct. There missing information. But where? Why? And how can they find it? In MS Word we have a lovely tool that reveals the non printing characters and therefore shows us how the page has been created and why the strange spaces occur. Paragraph toolIt would be rather useful to have the same sort of thing in Excel I have always thought. Yes, there are several different things we can use to reveal formulas for instance or dependant cells in a formula but we need more!

So, back to the imported data. Sometimes the data holds cells that seem at first glance empty. But they are not. What we don’t see is that there is a space character in the cell and therefore resulting in false positives, e.g. when we use the ‘COUNTA’ formula.

Here is an example of some data with seemingly empty cells. Example 1And here is the result if I use the ‘CountA’ function in that column. Result 1But wait! There are two cells in this column that are empty are there not?

Well, NO.

You cannot see them but there are space characters in each of those cells.

How to find a space character.

I will use the IF Function to find which cells have only a space in them.

  • In the column next to the Codes type the heading ‘IF’.
  • In the first cell type or use the function Wizard to create the formula.

This formula works

=IF(B2=” “,”True”,”-“)

This is a better formula

=IF(ISTEXT(B2),IF(LEN(TRIM(B2))=0,”Spaces”,”Valid text entry”),”Nothing”)

This is the result

Result 5The space and the hyphen are both encased with double quotation marks, which denote that what is between is text.

  • Autofill down the column.

The result will look like this.Result 2Here is the result where there are cells with nothing at all in them. Result 3You hopefully will only find a few cells that contain just a space. but this tip has just saved you lots of time.

You can now use the same sort of formula to locate other items or characters in cells.

Tips – Using a Nested IF Function in Excel

 

Using the SUMIFS function in Excel

 

LEAVE A REPLY

Please enter your comment!
Please enter your name here