Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Apr 30 '13 at 11:01

This question came from our site for professional and enthusiast programmers.

    
+1 good question ... but better suited to Super User :) –  brettdj Apr 30 '13 at 1:44
4  
FWIW an alternative to ISNUMBER is the rarely used N. Something like this =NOT(ISERR(N(--A1))) –  brettdj Apr 30 '13 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 Apr 30 '13 at 4:00

3 Answers 3

up vote 6 down vote accepted

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

=IsNumber(Trim(A1)*1)

share|improve this answer
2  
+1 for speed and a good anwser. It sucks when things are right in your face but you can't see them! –  glh Apr 30 '13 at 1:23
1  
Also I've found out that you dont even need to use trim. My regards. –  glh Apr 30 '13 at 1:24
2  
@glh sometimes it just takes another set of eyes to see what's right in front of ya. :) –  David Zemens Apr 30 '13 at 1:27

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.

share|improve this answer
1  
+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 Apr 30 '13 at 3:55

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

=N(-A1)

Thanks brettdj

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.