Forum Discussion

LiamSinton's avatar
LiamSinton
Copper Contributor
Jul 25, 2024

Replace partial text string from column based on value

I need to replace the value of column A with a value depending on part of the content of the cell in column A.

So let's say...

A2 = "Company-a2", I want to replace only the 'a' with a 'c', making the result "Company-c2" BUT if...

A2 = "Company-b2", I want to replace only the 'b' with a 'd', making the result "Company-d2"

Pulling my hair out with this one 🤪

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 25, 2024

    LiamSinton 

    Here is a formula that should work in all versions of Excel.

    In could be streamlined Excel in Microsoft 365.

     

    =LEFT(A2, FIND("-", A2))&CHAR(CODE(MID(A2, FIND("-", A2)+1, 1))+2)&MID(A2, FIND("-", A2)+2, 10)

Resources