Check if Excel cell text is numeric using formula only
Solution 1:
Try multiplying the cell value by 1, and then running the IsNumber
and Trim
functions, e.g.,:
=IsNumber(Trim(A1)*1)
Solution 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.
Solution 3:
The shortest answer I've got to my question is:
=N(-A1)
Thanks brettdj
Solution 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.