LEFT function

[Guest]

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

 

[Biff]

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

 

[Max]

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

 

[Roger Govier]

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)