I have a range that should only contain numbers. Blanks are OK, but I need to determine if anyone “deleted” a cell by typing a space or entered text in any other way. This worksheet formula returns TRUE if there is text in the range:
COUNTA counts all the non-blank cells while COUNT counts only numeric cells. If I were trying to prevent improper data entry, I would probably use Data Validation to ensure that only numbers are entered. Data Validation has an option to only allow decimals (or whole numbers). But you can’t just leave it at that. You have to define a range of decimals to allow. To allow any number, you can set the criterion to less than or equal to fifteen 9s. Excel can hold numbers larger than that, but it’s unlikely you program allows them.
If you need to allow really large numbers, you can use custom validation with a formula to check for text.
I’m not try to prevent data entry, though. I don’t have control over the worksheet, I just need to check for the error condition in code. So I used:
Application.WorksheetFunction.Count(rng.Value) Then
Err.Raise 9999, , “Range contains text”
End If
could this be done with a ‘Go To > Special’. Just select the range you’re interested in, goto-special and then select ‘constants’ and then ‘text’. If it says ‘no cells were found’ then hey presto…
Rob
You could simplify the code using COUNTIF and wildcards:
COUNTIF(A1:C6,”*”) Ignores numbers and error values; counts empty strings and cells containing text
COUNTIF(A1:C6,”?*”) Ignores numbers and error values; counts cells containing text, but not cells containing empty strings
Brad
[…] Daily Dose of Excel » Blog Archive » Finding Stray Text […]
Why the custom validation formula =NOT(ISTEXT(..)) rather than either =ISNUMBER(..) or =COUNT(..)? To allow entry of booleans and error values in addition to numbers?
No, ISNUMBER is the right function. I usually end up with NOT ISTEXT because I type ISNUMERIC (which isn’t a worksheet function) and get frustrated when it complains. :)