Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2011-02-27
Medium Priority
Last Modified: 2012-05-11

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?


f a cell
Question by:Steve_Brady
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
LVL 81

Expert Comment

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


LVL 50

Accepted Solution

barry houdini earned 2000 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




[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


regards, barry
LVL 50

Expert Comment

by:barry houdini
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 34992912
Thanks for the responses.


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:


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.  :)

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.  :)
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 2000 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


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


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
LVL 50

Expert Comment

by:barry houdini
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.


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

LVL 81

Expert Comment

by:zorvek (Kevin Jones)
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...


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.


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.
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.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question