Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
The left function does not work when displaying times, how is thi. | Excel Worksheet Functions | |||
Trim function doesn't clean out ASCII Code 160 (Space) | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
left worksheet function within a combo box | Excel Worksheet Functions |