Code / Formula for Renaming Text in a Long Excel List

irfman

New Member
Joined
Jan 1, 2019
Messages
7
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
 

Attachments

  • Sample List.jpg
    Sample List.jpg
    174.6 KB · Views: 11

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

mamady

Board Regular
Joined
Sep 23, 2011
Messages
201
Office Version
  1. 365
Platform
  1. Windows
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 TextReplacement Text
M/S
CompanyCo
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
1596474953523.png



Best Regards
 

irfman

New Member
Joined
Jan 1, 2019
Messages
7
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.
 

mamady

Board Regular
Joined
Sep 23, 2011
Messages
201
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,634
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top