-
Find and replace multiple items
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
-
No need for a macro. Use a VLOOKUP formula.
-
[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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
My problem
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
-
To automate this, you would need a list of paired names. My code can be easily adapted to loop through such a list.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules