PDA

View Full Version : Find and replace multiple items



shonshon
10-22-2008, 01:58 PM
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
10-22-2008, 02:08 PM
No need for a macro. Use a VLOOKUP formula.

mdmackillop
10-22-2008, 03:18 PM
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

shonshon
10-22-2008, 07:03 PM
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
10-23-2008, 12:48 AM
To automate this, you would need a list of paired names. My code can be easily adapted to loop through such a list.