IF Formula not recognizing conditions (no errors just wrong answer)

[Chelsea0270516]

Hello!

My goal is to filter through some three/four digit IDs. Any three digit ones need to be ignored. Any that end with a 1,2,3, or 9 need special attention. Those numbers need to drop the last digit.

Sample: 2309 needs to become 230

I have a nested IF statement that isn't working. Normally I don't have issues with the logic in them but I can't see why this wouldn't work.

First I test for the 3 digit ones with:

IF(LEN(A3)=3,A3, ...) - this part works just fine.

Next I have a series of Nested if statements to test for each condition using Right(A3,1)=1 (or 2,3,9) as my logical test. Since it is an equals sign that should work & return a true/false answer but it doesn't appear to be working. The answer for a true result should be Left(A3,3) but that isn't working. The answer for a false should be just A3. Full formula below.


=IF(LEN(A3)=3,A3,IF(RIGHT(A3,1)=1,LEFT(A3,3),IF(RIGHT(A3,1)=3,LEFT(A3,3),IF(RIGHT(A3,1)=2,LEFT(A3,3),IF(RIGHT(A3,1)=9,LEFT(A3,3),A3)))))

 

[wideboydixon]

Use quotes I would think:

=IF(LEN(A3)=3,A3,IF(RIGHT(A3,1)="1",LEFT(A3,3),IF(RIGHT(A3,1)="3",LEFT(A3,3),IF(RIGHT(A3,1)="2",LEFT(A3,3),IF(RIGHT(A3,1)="9",LEFT(A3,3),A3)))))

And if you want the answer to be a number:

=IF(LEN(A3)=3,A3,IF(RIGHT(A3,1)="1",LEFT(A3,3),IF(RIGHT(A3,1)="3",LEFT(A3,3),IF(RIGHT(A3,1)="2",LEFT(A3,3),IF(RIGHT(A3,1)="9",LEFT(A3,3),A3)))))+0

WBD

 

[AlanY]

the right() treated your 4 digits as text, change them to RIGHT(A3,1)="1" etc should work

 

[Marcelo Branco]

Or...

=IF(LEN(A3)=3,A3,IF(OR(RIGHT(A3)={"1";"2";"3";"9"}),LEFT(A3,3),A3))+0

M.

 

[mikerickson]

Treat the number in A1 like a number, not like a string

=IF(INT(LOG(A3))=2, A1, IF(OR(MOD(A3,10)={1,2,3,9}), FLOOR(A3,10)/10, A3))

 

[Chelsea0270516]

You guys are awesome!!!! That makes total sense. Also - Marcelo - I didn't know you could use brackets that way. Such a time saver!!!