+ Reply to Thread
Results 1 to 10 of 10

Nested IF LEFT and RIGHT THEN

  1. #1
    Registered User
    Join Date
    11-09-2018
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    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. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    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))
    Last edited by 63falcondude; 11-09-2018 at 11:50 AM.

  3. #3
    Registered User
    Join Date
    11-09-2018
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Nested IF LEFT and RIGHT THEN

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

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    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. #5
    Registered User
    Join Date
    11-09-2018
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    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. #6
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,627

    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. #7
    Registered User
    Join Date
    11-09-2018
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    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. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    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. #9
    Registered User
    Join Date
    11-09-2018
    Location
    USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Nested IF LEFT and RIGHT THEN

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

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Nested IF LEFT and RIGHT THEN

    You're welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Nested IF/THEN with INDEX MATCH and return LEFT value
    By Aquarock in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-17-2018, 11:56 AM
  2. [SOLVED] Excel - IF OR LEFT nested statement
    By used2reg in forum Excel General
    Replies: 2
    Last Post: 09-08-2017, 08:42 AM
  3. (SUM(IF( ARRAY Function with nested LEFT Rule - Help Needed
    By huntethic in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-16-2016, 05:05 PM
  4. Nested Formula Help - LEFT and Substitute
    By keith.will in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2015, 01:55 PM
  5. Nested if and Left and right statements
    By Dante_sparda in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-05-2012, 03:09 PM
  6. Nested Vlookup and Right/Left Function
    By sheclaire in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-15-2009, 03:50 AM
  7. Nested Vlookup and Right/Left Function
    By sheclaire in forum Excel General
    Replies: 1
    Last Post: 01-15-2009, 03:11 AM

Bookmarks

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