20

I have to find out if my cells text is a numeric value and wanted to use an elegant non VBA method that doesn't impede on its current state or value.

What I've found is that the ISNUMBER() function only works if the cells are number formatting or has no spaces if text formatting e.g.:

number as text examples

For the first three I've used =ISNUMBER(...) and my last attempt is =ISNUMBER(TRIM(...)).

The only method I've used that doesn't use VBA is to override my current values using text to columns then use the =ISNUMBER() function.

Note: I am proficient with VBA and Excel and understand I could create a user-defined function. But I don't want to as this imposes a macro required workbook or an add-in to be installed, which I can and have done in some cases.

I will appreciate any advice, thoughts (even if they tell me it can't be done) or VBA solutions (won’t be marked as answer however).

Improve this question
5
  • 4
    FWIW an alternative to ISNUMBER is the rarely used N. Something like this =NOT(ISERR(N(--A1)))
    – brettdj
    Commented Apr 30, 2013 at 3:54
  • 1
    @brettdj thanks for that! For an answer that uses the least number of charaters and is quick and dirty I've now got =N(-A1). Didn't know that existed either.
    – glh
    Commented Apr 30, 2013 at 4:00
  • What is the purpose of the hyphen(s) in front of the cell reference? And why do you each have a different number of hyphens?
    – youcantryreachingme
    Commented May 21, 2018 at 8:25
  • 1
    @youcantryreachingme it's a minus sign. it changes the sign of a number. It's an excel trick to automatically convert a numeric string into a numeral. I can't speak for brettdj but I'm guessing they used -- out of habit, as it's commonly used in more complex SUMPRODUCT formulations and things of that nature. using -- converts a numeric string to a number while preserving its sign (ie. positive numerals stay positive, negatives stay negative).
    – Alex M
    Commented Oct 5, 2020 at 21:24
  • Thanks Alex. Cool trick.
    – youcantryreachingme
    Commented Oct 6, 2020 at 21:38

6 Answers 6

Reset to default
44

Try multiplying the cell value by 1, and then running the IsNumber and Trim functions, e.g.,:

=IsNumber(Trim(A1)*1)

Improve this answer
5
  • 2
    +1 for speed and a good anwser. It sucks when things are right in your face but you can't see them!
    – glh
    Commented Apr 30, 2013 at 1:23
  • 1
    Also I've found out that you dont even need to use trim. My regards.
    – glh
    Commented Apr 30, 2013 at 1:24
  • 2
    @glh sometimes it just takes another set of eyes to see what's right in front of ya. :)
    – David Zemens
    Commented Apr 30, 2013 at 1:27
  • Doesn't work for me as "3/4" is not a number, however it is treated as if it were a date and returns 44989
    – alexkovelsky
    Commented Jul 7, 2023 at 10:56
  • 3/4 is a number. A date is also a number. You may need to do some extra workaround to handle date conversions, and if you only consider "whole numbers" you may need extra condition to handle that, as well.
    – David Zemens
    Commented Jul 7, 2023 at 14:28
5

Assuming the value you want to convert is in A1 you can use the following formula:

=ISNUMBER(VALUE(TRIM(CLEAN(A1)))

Here the functions clean and trim are removing whitespace and none printable characters. The function value converts a string to a number, and with the converted string we can check if the value is a number.

Improve this answer
2
  • 4
    +1. As a quick and dirty I like =VALUE(A1) as my data won't have the need for CLEAN(). I didn't know the VALUE() function existed. Just to note the TRIM() does nothing as spaces are ignored anyway.
    – glh
    Commented Apr 30, 2013 at 3:55
  • "3/4" is not a number, but is treated like a date by "value"
    – alexkovelsky
    Commented Jul 7, 2023 at 10:57
4

The shortest answer I've got to my question is:

=N(-A1)

Thanks brettdj

Improve this answer
3

I know this post is old but I found this very useful in this case I had a formula that returned (333), even though it is a number and ISNUMBER will say it is a number even though I did not want an answer if it had characters other than digits. The following worked for me.

=IF(AND(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))),"Is Number","")

It works if there is ANY characters other than digits. If you just want a true false drop the IF

=AND(ISNUMBER(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

As David Zemens stated

=IsNumber(Trim(A1)*1)

Works but if there is a "-" or the number is in parentheses it will say it is a number.

I hope this helps you or others.

Improve this answer
0

if anyone needs to filter cells that contain anything that is not numeric:

=AND(SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))=LEN(A1),A1<>"")

decimals and negatives result FALSE

Improve this answer
0

I'm pretty late to the party, but, possibly the easiest, or shortest genuine test (note that the N function converts the number) is =ISNUMBER(--A1).

Using the examples you posted above...

Testing values using ISNUMBER

As with a lot of Excel shortcuts, the '--' forces Excel to assume the value afterward is a number, then all spaces are ignored. It's as if you had typed --123 directly into a box (-- obviously gives a positive number).

The third example also shows that other textual values in the string don't simply cause an error output for ISNUMBER.

Number ISNUMBER() Format Comments
123456 TRUE Numeric Plain number
123456 TRUE Text Number as text
123456A FALSE Text Number with 'A' tagged on the end
123456 TRUE Text Number prefixed with four spaces
Improve this answer
2
  • "3/4" is number, because it is treated as date. But this should not be a number.
    – alexkovelsky
    Commented Jul 7, 2023 at 11:00
  • 1
    @alexkovelsky: Well, you could tag a test for a date on there, too, such as =AND(ISNUMBER(--A3),NOT(IFERROR(SEARCH("/",A3),FALSE))), though it does start to get messy.
    – Paul
    Commented Jul 7, 2023 at 13:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .