Extracting and replacing - whats a girl to do?!

Copper Contributor

Hi there, having a hard time find formulas for my issue. 

 

I need a formula that will search the entire "Stock Codes" column, and replace new information in the "Available" column...Tasks include:

1. Any row in the "Stock Codes" column that has the letter R in it will be replaced with RF in the "Available" column...
2. Any row in the "Stock Codes" column that DOESN'T have the letter R in it will be replaced with RM in the "Available" column...

Is there one formula that can do both items listed?

This girl can't figure it out, any help would be appreciated. xo!

 

Val

 

 

 Stock Spreadsheet.jpeg

 

 

 

12 Replies

TRY:

place below formula adjacent to the column of data (column B) and copy down..
=IF(IF(ISERROR(SEARCH("R",A2)),0,1),SUBSTITUTE(A2,"R","RF"),A2&"RM")

Thank you much for responding, I made some edits to my question right after you responded...I forgot one item...

my reply seems to be what you needed...

Just tried it, its really close, the only issue is that the number is displayed in the column B, the letters are correct though. Stock Spreadsheet column B.jpeg

pls see attached sample..

pls see attached.

hope this is what you needed.

thanks..

I see the same on my end, I just need the RF and RM in column B, I dont need to keep the numbers in column B....you are so close my friend

Thank you so much for helping me

sorry I send the wrong file - but have sent you the correct one

attached is the revised file..

I see it!!! thats it, you're amazing! Spent hours trying to figure it out....ughh

Thanks so much! 

xoxo

Val

I misinterpret your first OP, at least we got it.
glad to help..
there is another formula (if you're interested)
=IF(ISNUMBER(SEARCH("R", A2)),"RF","RM")