Nested IF LEFT and RIGHT THEN

1. Nested IF LEFT and RIGHT THEN

Hello,

Job Codes =

EX12.01
EX24.02
EX33.03

I need a nested statement that says:

IF Job Code begins with "EX" and and ends in "1", THEN result = "TIER 1"
IF Job Code begins with "EX" and and ends in "2", THEN result = "TIER 2"
IF Job Code begins with "EX" and and ends in "3", THEN result = "TIER 3"

Thank you!

2. Re: Nested IF LEFT and RIGHT THEN

Hello and welcome to the forum.

Assuming that your job codes are in column A starting in A2, try this:

="TIER "&IF(LEFT(A2,2)="EX",CHOOSE(RIGHT(A2),1,2,3))

3. Re: Nested IF LEFT and RIGHT THEN

IT WORKS! Thank you very much for your quick response. Much appreciated :-)

4. Re: Nested IF LEFT and RIGHT THEN

You must have seen it before I edited. It will not work in all scenarios. I would use the one that is now in post #2.

5. Re: Nested IF LEFT and RIGHT THEN

Hi, thanks for the update. What if I have a job code that is EX23.1X (doesn't end in 1,2,3), how do I get it to reflect "TIER 1"? And anything that doesn't meet this requirement is "INVALID"?

6. Re: Nested IF LEFT and RIGHT THEN

you need to give some examples and the solutions you wish

="tier"&IF(LEFT(A2,2)="EX",IFERROR(RIGHT(A2,1)*1,MID(A2,LEN(A2)-1,1)),"")

or

="tier"&IF(LEFT(A2,2)="EX",IFERROR(RIGHT(A2,1)*1,"Invalid"),"")

7. Re: Nested IF LEFT and RIGHT THEN

Job Code TIER
EX00.01 1
EX01.02 2
EX02.03 3
EX04.7X 1
EX23.8L 1
GL64.13 INVALID

So basically, if it doesn't begin with EX, it is "INVALID". If it does begin with "EX", but doesn't end in 1,2,3, then it defaults to Tier 1. Otherwise, it is Tier 1, 2, or 3.

8. Re: Nested IF LEFT and RIGHT THEN

Based on post #7, you can use this in B2:

=IF(LEFT(A2,2)="EX",IF(OR(RIGHT(A2)={"2","3"}),RIGHT(A2)+0,1),"INVALID")

9. Re: Nested IF LEFT and RIGHT THEN

This exactly what I needed. Works like a charm. THANK YOU!

10. Re: Nested IF LEFT and RIGHT THEN

You're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1