Very tricky Find and Replace Strings - VBA code needed!

[actjfc]

Excel Friends:

I have list of strings in one Column, i.e. Column A, with a variable number of rows. Also, I have a Table of two columns filled with strings in each cell, let’s say Column C, and D, with a variable number of rows. The idea of the VBA macro that I would like to get help is this:

The macro should loops throughout the whole column A, any time any of the strings in a cell in Column C of the table appears inside any of the strings in column A, then the macro goes on creating a copy of the column A, in Column F, but replacing the string found with the value in Column D in the same row of the table.

For example:
Column A:The House is red
Column C - D:
House - Split Level
Red - White

Column F: The Split Level is white.

It needs to loop hundreds of rows

Thanks for any help!

 

[actjfc]

Amazing! It works perfect! Short and neat, this code logic is exceptional to later trim it! I still have to take a lengthy look at it to fully understand it:

s = " " & Replace(Replace(aData(i, 1), ",", " , "), " ", " ") & " "

Thanks Peter_SSs!

 

[Peter_SSs]

Amazing! It works perfect! Short and neat, ..

You are welcome.


Note that post #21 doesn't display that line of code accurately as the normal body of a post replaces multiple consecutive spaces with a single space. If we enclose in code tags, you will note the red part encloses two spaces, not one.

s = " " & Replace(Replace(aData(i, 1), ",", " , "), " ", "  ") & " "

 

[actjfc]

I realized it now. I did copy the right one into my workbook. Thanks again!