# Check if Excel cell text is numeric using formula only

## All we need is an easy explanation of the problem, so here it is.

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

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

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

`=IsNumber(Trim(A1)*1)`

### Method 2

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.

### Method 3

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

``````=N(-A1)
``````

Thanks brettdj

### Method 4

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.

### Method 5

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`

### Method 6

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0