Solved

# Identify cells with numerical values even tho formatting is non-numeric in Excel

Posted on 2011-02-27
481 Views
Hello,

In Excel (2007), suppose a cell has some numerical value (e.g. 1858) but the cell formatting is set to General (i.e. not Number), is there a way -- using =ISNUMBER() or some other function  -- to determine that the value is numerical?

In a broader sense, can a cell, regardless of it's formatting, which has only digits (0-9) and no other characters or symbols (except a period if it involves decimal places), be distinguished from cells which do not meet those criteria?

Thanks

f a cell
0
• 4
• 3
• 2
• +1

LVL 81

Expert Comment

ID: 34992730
To test whether or not a text string is a valid number use the ISNUMBER function as illustrated in the example below.

=ISNUMBER(A1*1)

Kevin
0

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 34992737
Yes, Steve

The formatting sometimes "lies"......because if you change a text-formatted column to number format that has no effect, so one way to determine whether a cell is numeric.....or not, is to use

=ISNUMBER(A1)

also

=COUNT(A1)

[which would return 1 or zero, numeric or not]

If you want to tell whether the cell has numbers which could be co-erced to a number, e.g. if you want to count 23 whether it's text-formatted or not then try

=ISNUMBER(A1+0)

regards, barry
0

LVL 50

Expert Comment

ID: 34992771
Sorry, I didn't see Kevin's response. *1 will do the same as +0, i.e. "coerce" to a number (if possible) without changing the value. If the number can't be coerced then you'll get an error and ISNUMBER will return FALSE

Sometimes you might get what you would regard as a "false positive" using that method, e.g. if A1 contains 1-2 then when you coerce that it becomes a date so ISNUMBER(A1+0) would give you TRUE

regards, barry
0

Author Comment

ID: 34992912
Thanks for the responses.

>>=ISNUMBER(A1*1)
>>=ISNUMBER(A1+0)

I can see how modifying the target value (by either ...*1 or ...+0) eliminates false negative results* (i.e prevents the search from missing numerical values which happen to have non-numerical formatting)  -- and that was indeed the specific question I asked.

However, in testing your solutions, I discovered that both of them introduce false positives into the results (i.e. returns TRUE for some cells which are not really numbers).

The example here is if =ISNUMBER(A1*1) or =ISNUMBER(A1+0) is used when A1 is empty in which case, both solutions return TRUE.

That's interesting because it treats an empty cell and a cell containing 0 (zero) the same.  Clearly the TRUE response when A1 contains 0 is accurate but does multiplying "blank" or "empty" by 1 or adding zero to them result in a number?  In a theoretical or mathematical sense, it seems like the answer should be "NO."  I don't know. However, in a practical sense, it appears to require something like:

=AND(A1<>"",ISNUMBER(A1*1))

to avoid the false positives.

Is that how you would do it or is there some other single function or better way?  I am also curious about why your solutions return TRUE for empty cells -- if you care to comment.

Thanks again

*Please forgive my including definitions of false negatives and false positives if you are already familiar with those terms.  :)
0

Author Comment

ID: 34992919
>>Sometimes you might get what you would regard as a "false positive"

And sorry, Barry.  I did not see your response.  :)
0

LVL 50

Assisted Solution

barry houdini earned 500 total points
ID: 34993079
Hello Steve,

I didn't consider blanks but, yes, that would be consistent with other Excel behaviour, e.g. if A1 is blank and you use this formula in B1

=A1

then B1 displays zero

I think it might be difficult to rule out all false positives and false negatives. To ensure that A1 contains only digits and decimal points then you could do that explicitly....and check that the value is numeric when co-erced....and check that it's not blank, i.e. using this formula

=AND(A1<>"",SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{".",1,2,3,4,5,6,7,8,9,0},"")))=LEN(A1),ISNUMBER(A1+0))

that would also stop 1-2 being regarded as numeric......and some other text values (e.g. some text can be regarded as a scientific number)

...but then it might rule out scientific numbers that you might consider should be TRUE, e.g. 9.9E+307

regards, barry
0

LVL 50

Expert Comment

ID: 34993103
....and with one more amendment you can check whether A1 is a number uncoerced too, that will allow scientific numbers too, i.e.

=OR(ISNUMBER(A1),AND(A1<>"",SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{".",1,2,3,4,5,6,7,8,9,0},"")))=LEN(A1),ISNUMBER(A1+0)))

....but the term "overkill" might come to mind :)

barry
0

LVL 81

Expert Comment

ID: 34993176
Maybe the real question is: why do you care?

Diminishing returns comes to mind.

Handle the most obvious cases while applying the 80/20 rule. You identified an empty cell as being not numeric so...

=AND(A1<>"",ISNUMBER(A1*1))

is a virtually perfect solution.

Adding more logic to the problem will be more like whack-a-mole than a real solution. For example, using Barry's formulas above return false for "-5" which is a perfectly valid number.

Kevin
0

Author Comment

ID: 35074087
Thanks for the feedback.  I guess my answer is that I like to learn as much as possible and asking follow-up questions commonly results in more learning -- and even more questions.

In fairness to you experts though, I really should remember to ask them as new threads.  Someone mentioned that very thing to me once before and I need to try to remember it more.  As you correctly pointed out, I had a great answer here and should have opened a new thread for follow-ups.

One thing I can say however, is that I am constantly impressed by and appreciative of the people who devote extra time and effort sticking with tricky problems and who don't rest until the right answer is found.  Barry up above here is one of them and teylyn is another.  I shouldn't start mentioning names because there are so many, but a big THANKS to all of you.

BTW, I wasn't before and I never would be upset if an expert says something like, "Hey, I'm happy to answer that question but it should probably go in a new thread."  That's a no-brainer and I can't imagine that any asker would have a problem with it -- I certainly would not.

Thanks again for the reminder zorvek.
0

LVL 24

Expert Comment

ID: 35356948
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0