Every so often you may encounter a problem with Excel where a function is not working properly, even though you know that your function syntax is correct. And when you manually check the data you find that the function is not returning the correct answer.
So what is going on?
This problem happened to me again recently and it took a little while for the penny to drop as it is fairly rare, but highly annoying when it does.
And it is down to something called “non-printable characters”. These pesky characters are invisible, which makes it doubly difficult to identify. These tend to crop up in reports that have been downloaded from other systems, which for reasons unknown, slip in characters of some sort that cannot be seen or identified on screen, but nevertheless cause mayhem with a number of functions.
As an example I’ll use the data which caused me the most recent problem.
The company I work for uses a web based database to manage courses and delegates and some information was extracted to generate a catalogue within a workbook (sample above). I wrote some VBA code to help users find courses either by name of ID number and in one instance, the dreaded end-debug screen kept on appearing.
The form I created allows users to enter partial names or IDs and search for information based on that partial string. When they entered the number 26, a message told them that two records had been found.
This was done using a COUNTIF function. However, there were in fact 6 records that contained the number 26.
So on the one hand the COUNTIF function was finding 2 records, but the loop in the code which populated an array in the memory was crashing as it found a third record that contained the number 26. The dynamic array was not big enough as the size was recalculated based on the findings of the COUNTIF function.
Initially I looked at formats – text vs. numbers for example.
I also looked for any spaces that might have caused an issue.
I manually added a COUNTIF function in the worksheet to double-check the findings of the function created in VBA but this gave the answer 2 as well.
Finally, realising that this was an extract from a non-Excel source, I tried using the CLEAN function suspecting that there may be some non-printable characters in there somewhere. The CLEAN function removes all non-printable characters from text. The syntax is very simple:
In a new column I wrote this formula and then copied all the “cleaned” text values and used PASTE SPECIAL VALUES over the original cells and retested the COUNTIF function.
…and hey presto…it found all 6 records.
So next time your functions are not working and seem incapable of identifying values in cells, try applying the CLEAN function to your data, it might just be the solution to your problem.