Solved

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

Posted on 2011-02-27
11
482 Views
Last Modified: 2012-05-11
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
Comment
Question by:Steve_Brady
  • 4
  • 3
  • 2
  • +1
11 Comments
 
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.

   =ISNUMBER(A1*1)

Kevin
0
 
LVL 50

Accepted Solution

by:
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

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
0
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Comment

by:Steve_Brady
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

by:Steve_Brady
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

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

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.

=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

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

=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

by:Steve_Brady
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

by:broomee9
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

Featured Post

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel User Form VBA Help 18 32
populate Excel dropdown via ADO and VBA 6 17
Excel Charts: How is this Chart made? 8 29
sort column using vba 2 19
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

809 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