Parse cell for 1 of 3 values, return corresponding string value

  • A cell will contain the words "GREEN", "YELLOW", or "RED". FWIW, the words will be surrounded by asterisks (e.g, "***GREEN***").


    I am trying to craft an elegant formula that will parse the cell and return "Green", "Yellow", or "Red".


    This is the formula I have so far, but it errors out if the original cell does not contain the first color ("GREEN"):


    Code
    =IF(FIND("GREEN",'(BC-1)'!A107)>0,"Green",IF(FIND("YELLOW",'(BC-1)'!A107)>0,"Yellow",IF(FIND("RED",'(BC-1)'!A107)>0,"Red")))


    I could add "iferror" I guess, but there may be a better way?


    Code
    =IFERROR(IF(FIND("GREEN",'(BC-1)'!A107)>0,"Green"),IFERROR(IF(FIND("YELLOW",'(BC-1)'!A107)>0,"Yellow"),IFERROR(IF(FIND("R",'(BC-1)'!A107)>0,"Red"),"***ERROR***")))


    Is there a more elegant solution? Thanks.

  • Re: Parse cell for 1 of 3 values, return corresponding string value


    Bonus points for you, too, if you provide us with a sample workbook to work with. ;)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Parse cell for 1 of 3 values, return corresponding string value


    Try:


    [COLOR="#0000FF"]=IFERROR(LOOKUP(9.99999999999E+307,SEARCH({"green","yellow","red"},'(BC-1)'!A107),{"green","yellow","red"}),"***ERROR***")[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Parse cell for 1 of 3 values, return corresponding string value


    Before I look, have you tried NBVC's suggestion?

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!