This is a worm in my head
@ Colin Legg
I'm not explaining myself very well, the UDFs were just another stab at trying to identify Null Strings
Thanks for the tip.
@ shg
Thanks for that it helps to see what is going on.
The reason for pasting the values was only to replicate something that might happen in the life of a worksheet (with the original data deleted or pasted over) and then the new column would contain Null Strings and go unnoticed until something like this was tried
=COUNTA(D:D)
This returns 21 in the example given, but there are apparently only 4 values in the column
The answer is correct because there are also 17 Null Strings.
This is fairly obvious on a small sample, but if we were talking about a few thousand rows with only a few Null Strings then this would not be so easy to detect.
The Null Strings can be detected with, in a helper column, say E
Drag/Fill Down
Then
will return the expected result of 4, but this seems a bit clumsy.
Either of these
or
will return the expected result, but I don't think that would be an intuitive solution, especially so if the Null Strings were not anticipated.
The yellow cells in the attached are conditionally formatted to highlight Null Strings
Add a value to either of these columns (alpha or numeric) to see what is bugging me.
Can any one see a cleaner solution to solve this problem, or am I just paranoid about this?
Bookmarks