Find strings within cells and replace with string from a table

Good morning all, hopefully you can help me.

I have a column of data which I need to tidy up. The data is made up of various strings, and I wish to replace various portions of those strings, with a predefined sanitised string contained within a table.

In the example below, I wish to search to recursively through each string, and see if a match can be identified against the bold text, with a clean string substituted in.

BEFORE

63,Euroconsult Mott Mc.Donald,144.100,MM Croydon, BoardRm,
64,MM Arnhem,144.100,Mott MacDonald, Croydon, Mezz,
64,MM Arnhem,144.100,MM Croydon, BoardRm,

AFTER

63,MM Arnhem,144.100,MM Croydon,
64,MM Arnhem,144.100,MM Croydon,
64,MM Arnhem,144.100,MM Croydon,

The sanitised data is contained in a separate table in the following format:

COLUMN A

MM Croydon, BoardRm
Mott MacDonald, Croydon, Mezz,
Euroconsult Mott Mc.Donald

COLUMN B

MM Croydon
MM Croydon
MM Arnhem

I have attempted to use the SUBSTITUTE function, contained within nested IF functions, but above two or three different levels, the formulae becomes to unwieldy, as my sanitised table is about 40 rows long.

Any help most appreciated.

Regards,

S

Originally Posted by Bob Phillips

I think I had some old code in my module that I inadvertently copied in. You should be able to get rid of test procedure and just run the ReplaceText procedure.

Thanks for the continued help.

I tried again, just using the ReplaceText procedure, but no luck. If you are still willing to help, attached is a workbook containing my name change sheet, and a very short extract of the data I am attempting to clean up.

Thank you

Stephen

P.S One final thought, would your method work if the string were mentioned more than once in the same string?

Stephen,

It needed a few small changes to reflect the data you actually have.

Before running the macro, select the sheet to be tidied up.

Please Login or Register  to view this content.