Text vs Numbers

H

Howard

I have a bunch of numbers like 123-5-30 and bunch of text in a column. I
want to use a "if" formula that gives me a "Yes" for number and a "No" in
adjacent column. Can you help? I tried it but it sees the numbers as text
and provides a No for both situation.
 
D

David Biddulph

123-5-30 is text, not a number
If you want to test for whether it would have been a number if you hadn't
put the hyphens in, use the test =ISNUMBER(--(SUBSTITUTE(A1,"-",""))) so
perhaps
=IF(ISNUMBER(--(SUBSTITUTE(A1,"-",""))),"Yes","No")
 
B

Bernard Liengme

I like David's answer but here is another
=IF(ISNUMBER(--LEFT(A1,2)),"Yes","No")
will test if the first two charters in tour text an digits - you can alter
the 2 as needed
best wishes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding the proper style field and the numbered paragraph value within the style 0
Finding the right style field in VBA 0
How can the font and size of (all) citation numbers be altered? 1
Excel does not accept text format! 0
Macro to select and delete levels of a document 2
Inconsistent formatting of section numbers 0
Footnote references on one page, footnote text on the next 5
Page numbering with Merged Document and IF statements 1

Top