Finding cells with numbers

J

jezzica85

Hi all,
Does anyone know if there's a way to structure an if statement like:

IF(cell A1 contains a number),show cell A1,leave default FALSE

I've tried wildcards and I can't figure it out. I want to be able to make
this work if any number 0-9 is the first character in the cell, so it would
catch things like:

1
1939
20/20
1024-bit

but not things like:
Route 90
T1000 (I'm a T2 fan, can you tell? :) )

Thanks a lot!
jezzica85
 
B

Bob Phillips

=IF(ISNUMBER(A1),A1,FALSE)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
J

JudithJubilee

I've been playing with this prob and the following formula works although it
is rather long!

=IF(OR(LEFT(A1,1)="1",LEFT(A1,1)="2",LEFT(A1,1)="3",LEFT(A1,4)="1",LEFT(A1,5)="1",LEFT(A1,6)="1",LEFT(A1,7)="1",LEFT(A1,8)="1",LEFT(A1,9)="1",LEFT(A1,1)="0"),A1,FALSE)

Someone probably has an easier solution though so wait around!

Judith
 
B

Bob Phillips

=ISNUMBER(--LEFT(A1,1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bryan Hessey

You could try


=IF(OR(LEFT(A1,1)={"0","1","2","3","4","5","6","7","8","9"}),"yes",FALSE)

and leave the ,False off if you just wish it to default to False.

--
 
B

Bob Phillips

Judith,

You got your test value mixed up with the character position after 3

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
J

jezzica85

Thank you Bryan, this works great! And to everyone else, thanks for your
solutions too, I really appreciate it.
Jezzica85
 
B

bplumhoff

Hello,

=ABS(CODE(LEFT(A1,1))-52.5)<5

or

=IF(ABS(CODE(LEFT(A1,1))-52.5)<5,"yes","no")

HTH,
Bernd
 
Top