Code / Formula for Renaming Text in a Long Excel List

[irfman]

Hi:

I work in a company with sales to many customers. At the month end, I am required to review and manually correct the names of customers maintained in an excel file which is a very cumbersome process using the find and replace all option. Is is possible if this process can be automated through a formula or code?. I have attached a sample list showing the original text / character that is to be replaced with the text of my choice. The excel files have just names so I am not attaching it. Hope I have explained my query well. Looking forward to usual help and assistance.

Thanks & Regards

 

[mamady]

Hi Dear,

I am not a VBA expert, but this can be done with a formula.

First, you need to create the reference table you attached in your post in a helper sheet/range.
For example,

Original Text	Replacement Text
M/S
Company	Co

note: I typed double spaces in the replacement text cell for the above example

After this preparation, you can use the below formula:
=SUBSTITUTE (text, old_text, new_text)

For example
Cell A1 has an incorrect naming: Company ABC
Range C1:D3 has the above reference table
Cell B1 is where the output is desired, you can type the following
=SUBSTITUTE(A1,INDEX($C$1:$C$3,MATCH(TRUE,ISNUMBER(SEARCH($C$1:$C$3,A1)))),INDEX($D$1:$D$3,MATCH(TRUE,ISNUMBER(SEARCH($C$1:$C$3,A1)))))

it should look like the following screen-shot

Best Regards

 

[irfman]

thanks it is a good solution but it is case sensitive for e.g. if the original text in the reference table (column C) is in proper case (e.g. Company) and the text in column A in CAPs (COMPANY) then the formula wont recognize it. Any way to address the same? Thanks in anticipation.

 

[alansidman]

here is a tutorial on how to achieve your results with Power Query/Get and Transform.

Bulk Find And Replace In Power Query | How To Excel

www.howtoexcel.org

 

[mamady]

thanks it is a good solution but it is case sensitive for e.g. if the original text in the reference table (column C) is in proper case (e.g. Company) and the text in column A in CAPs (COMPANY) then the formula wont recognize it. Any way to address the same? Thanks in anticipation.

Use Find instead of Search in the formula, this should address the case-sensitive scenario