Check if a cell contains multiple values and return multiple specific values

Hi, I'm not great with formulas and hoping someone can help me. I am trying to create a formula that will check if the adjacent cell contains a number of values (b0, b1,b2, b3, b4, b4, bneg1, bneg2, bneg3, bneg4, or bneg5) and return the corresponding value (0, 1, 2, 3, 4, 5, -1, -2, -3, -4, -5) when the adjacent cell looks like this:

050613,16:57:18,af5632,habituation,b0,bluecircle12s_habit,1578,00011000,00000100,7000,0,0

or

050613,16:57:18,ag5939,extinctionearly,bneg5,redcircle19s,996,00000100,redcircle,17000,0,0


What I need to happen is if the cell contains "b0" the formula writes "0", if the cell contains "bneg5" the formula writes "-5" etc.

I have tried =IF(ISNUMBER(SEARCH("b0",R25)),"0") but I can't work out how to get the formula to search for multiple values and return a different value depending on what it finds in the adjacent cell.

Any help would be greatly appreciated.

Hollie

Hi, I've uploaded an example with a before and after sheet.

Thanks very much for you help!

See the attached file

Thank you so much!!! You've saved me so much time!

Originally Posted by hollieb

Thank you so much!!! You've saved me so much time!

You're Welcome!