LEFT function
 

I have a list of extension numbers - some begin with 1 - I wish to have a
column that shows "Ignore" against the numbers beginning with 1.

I thought the following formula would work:

=IF(LEFT(A1,1)=1,"Ignore",A1)

It looks like excel does not recognise the result from LEFT(A1,1)as 1 ?

Thanks

Andrew

Hi!

Try either one of these:

=IF(--LEFT(A1,1)=1,"Ignore",A1)

=IF(LEFT(A1,1)="1","Ignore",A1)

LEFT is a TEXT function. Everything it returns is TEXT.

Biff

-----Original Message-----
I have a list of extension numbers - some begin with 1 -
I wish to have a
column that shows "Ignore" against the numbers beginning
with 1.

I thought the following formula would work:

=IF(LEFT(A1,1)=1,"Ignore",A1)

It looks like excel does not recognise the result from
LEFT(A1,1)as 1 ?

Thanks

Andrew

.

Try instead: =IF(LEFT(A1,1)+0=1,"Ignore",A1)

"+0" is one way to coerce the
result from LEFT(A1,1) to a "real" number
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"AJPendragon" wrote in message
...
I have a list of extension numbers - some begin with 1 - I wish to have a
column that shows "Ignore" against the numbers beginning with 1.

I thought the following formula would work:

=IF(LEFT(A1,1)=1,"Ignore",A1)

It looks like excel does not recognise the result from LEFT(A1,1)as 1 ?

Thanks

Andrew

Probably because the 1 is text. Wrap the test 1 in quotes "1"
=IF(LEFT(A1,1)="1","Ignore",A1)
or force it to be numeric with the double unary in front of the formula
=IF(--LEFT(A1,1)=1,"Ignore",A1)

--
Regards
Roger Govier
"AJPendragon" wrote in message
...
I have a list of extension numbers - some begin with 1 - I wish to have a
column that shows "Ignore" against the numbers beginning with 1.

I thought the following formula would work:

=IF(LEFT(A1,1)=1,"Ignore",A1)

It looks like excel does not recognise the result from LEFT(A1,1)as 1 ?

Thanks

Andrew