daviddiebel
asked on
I would like a macro to perform many find and replace actions using a specified list.
Please see the attached workbook. I have a huge number of find/replace actions to perform. I will always be replacing numbers with other sets of numbers. I would like to paste the data on which to perform the find/replace action in the "Data" worksheet, and list the old values being replaced and the corresponding new values in columns A and B in the "Old and New Values" worksheet - I only want the find/replace actions to be performed on the "Data" worksheet.
I don't need a copy of the old data to be preserved. Sometimes the number strings might be in brackets or parentheses - I want to preserve those, and just replace the numbers.
I don't need a copy of the old data to be preserved. Sometimes the number strings might be in brackets or parentheses - I want to preserve those, and just replace the numbers.
Last Comment
If indeed this is your exclusive list of values to replace, you could just do a straight search and replace like so:
This would work regardless of the occurrence of other characters (like brackets).
If, however, your replacement list is more complex than this (i.e., substrings being mutually exclusive), then a macro would be needed.
Regards,
-Glenn
This would work regardless of the occurrence of other characters (like brackets).
If, however, your replacement list is more complex than this (i.e., substrings being mutually exclusive), then a macro would be needed.
Regards,
-Glenn
ASKER
I want to avoid having to enter many hundreds of numbers into the Find and Replace box, and this will be an ongoing task. That is an example list and not an exclusive one.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
hi David,
If the number strings might be in brackets or parentheses you may need to change "LookAt:=xlWhole" to "LookAt:=xlPart". If this is the case, you may need to be more wary about the order that the substrings are replaced as Glenn has mentioned.
hth
Rob
If the number strings might be in brackets or parentheses you may need to change "LookAt:=xlWhole" to "LookAt:=xlPart". If this is the case, you may need to be more wary about the order that the substrings are replaced as Glenn has mentioned.
hth
Rob
ASKER
I found this on an internet search.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
Find-and-Replace.xlsx