# 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.

• 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

Quote from AliGW;790787

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

Here ya go.

## Files

• 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.

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

Yes. His solution worked. Thank you.

## Participate now!

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