How can I perform multiple search & replace - all data in spreadsheet?

[Bucyruss]

I have a spreadsheet with three columns.

Column A contains a couple thousand lines of text.

Column B contains a list of values to search for in Column A

Column C contains a list of values to replace found values

I want to search the entire column a for values in "column B" and
replace with corresponding value in "column C". for example, search
column A for values in B1 and replace all matches with the value in C1,
and continue until all lines of text in column A have been examined.
Then do the same for the old/new pair B2/C2, then B3/C3, etc right down
the list. Column A may contain multiple instances of matches for any
given value in column B.

Note: This action does not replace the entire column a cell value.

Example: Column a Column B Column
C
-------------------------- ---------
---------
The wild fox jumps fox
dog
The cat ate the mouse cat owl
The bear saw the fox bear
deer
The cat ran from the fox
The piano played

Should produce this output:

The wild dog jumps
The owl ate the mouse
The deer saw the dog
The owl ran from the dog
The piano played

Any Idea how I could accomplish this?

 

[Charlie]

This one is not as simple as it seems. First of all, I provided a
"ReplaceAll" function that replaces multiple occurrences of words ("Replace"
does not); and second, I presume you are replacing whole words i.e.
Replace("Waterfowl", "owl", "dog") becomes "Waterfdog" which is not what you
want, so I added a section to append spaces. i.e., Replace(..., " owl ", "
dog ")

Public Function ReplaceAll(txt As String, FindAll As String, ReplaceWith As
String) As String
'
' the Replace function only makes one pass and the resulting replacement
may produce
' another substring requiring replacement - ReplaceAll will loop until all
substrings
' have been replaced
'
ReplaceAll = txt
If FindAll <> "" And FindAll <> ReplaceWith Then
Do While InStr(ReplaceAll, FindAll) > 0
ReplaceAll = Replace(ReplaceAll, FindAll, ReplaceWith)
Loop
End If
'
End Function

Dim Cell As Range
Dim iRow As Long
Dim LastRowInColA As Long
Dim LastRowInColB As Long
Dim FindStr() As String
Dim ReplaceWith() As String

LastRowInColA = ?
LastRowInColB = ?

FindReplace = Range(Cells(1, 2), Cells(LastRowInColB, 3)).Value
ReDim FindStr(LastRowInColB)
ReDim ReplaceWith(LastRowInColB)
'
' must append leading and trailing spaces to prevent
' Replace("Waterfowl", "owl", "dog") --> "Waterfdog"
'
For iRow = 1 To LastRowInColB
FindStr(iRow) = " " & FindReplace(iRow, 1) & " "
ReplaceWith(iRow) = " " & FindReplace(iRow, 2) & " "
Next iRow

For Each Cell In Range(Cells(1, 1), Cells(LastRowInColA, 1))
For iRow = 1 To LastRowInColB
Cell.Value = Trim(ReplaceAll(" " & Cell.Value & " ", FindStr(iRow),
ReplaceWith(iRow)))
Next iRow
Next Cell

 

[Charlie]

Let me retract the part about the ReplaceAll function. The Replace function
should work fine in your case. I use the ReplaceAll function for replacing
multiple occurrences of the same character! For example, to compress out all
multiple spaces into single spaces, "this is a test" --> "this is a
test", the Replace function may leave behind two or more consecutive spaces.

Compressed_string = Replace(OldStr, " ", " ") ' two spaces with one space.

If "Replace" finds three spaces in a row, two will be replaced with one
still leaving two behind. That's when the ReplaceAll function is usefull

Compressed_string = ReplaceAll(OldStr, " ", " ") ' two spaces with one
space.