Hi -

I am working on standardizing several survey workbooks by replacing old question numbers to a new code. For example, a question that was previous S1 should now be Q1. This would also be only for the portion of the cell that contains S1 >> S1_a should become Q1_a

In order to do this efficiently I have made a key that outlines old text and new text. Since this is for several workbooks, I am hoping to automate the find and replace. I have attempted to use a replace function in conjunction with isnumber, find, and index match. The challenge is essentially that all examples of replace appear to have specific text, and not matched to a row of text. (So i could replace "ABC" with "EFG" but I don't know how to replace cell A1 with A2.

Please don't judge the formula! If(isnumber(find(index($I$3:$I$6,Match(C10,,$H$3:$H$6,0)),replace(isnumber(find(index($D$3:$D$6,Match(C10,$C$3:$C$6,0)),C10,1)),1,Index($I$3:$I$6,Match(C10,$H$3:$H$6))))))

I have attached an example workbook.Example for Replacing through Formula.xlsx

Where original and desire text represent the output and original text and desired text represent the desired formula input.

Please let me know if you can help!