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.

daviddiebel

ASKER

Find-and-Replace.xlsx

Glenn Ray

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

daviddiebel

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

daviddiebel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Brockett

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

daviddiebel

ASKER

I found this on an internet search.