Find and replace multiple items

[shonshon]

Hi,
My Excel file has a column that has hundreds of text that I need to replace them with other text
for example:
Monica to cooks
John to jsmith

etc.

the find and replace command in Excel do this one at a time. I want to write a macro that I put into it all the text that needs to replaced and the corresponding text (e.g. Monica and cooks) and etc. and it does this for me at once
Thanks alot
Shon Shon

[fb7894]

No need for a macro. Use a VLOOKUP formula.

[mdmackillop]

[VBA]Sub Macro1()
Dim DoReplace As String, Fnd As String, Rpl As String

DoReplace = InputBox("Enter words to find and replace eg no : yes")
Fnd = Trim(Split(DoReplace, ":")(0))
Rpl = Trim(Split(DoReplace, ":")(1))

Selection.Replace What:=Fnd, Replacement:=Rpl, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
[/VBA]

[shonshon]

Thanks alot,
I attached an example of my problem. the size of my search is 1 column but it i have to find and replace 50 items
also as you can see any cell in this column can have any combination of these items
Shon Shon

[mdmackillop]

To automate this, you would need a list of paired names. My code can be easily adapted to loop through such a list.