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