How to check and batch replace text within a formula in an Excel spreadsheet possibly hundreds of files
thousands of excel files that use a VLOOKUP command to get data from another spreadsheet. a problem caused some of them to change their formulas i have figured out what the incorrect strings are and can do a find/replace in each formula but cant manually do that for each one.
additionally, some files are fine, some have "problem 1" text and some have "problem 2" text to be replaced .
any ideas for an automated way to do this?
additionally, some files are fine, some have "problem 1" text and some have "problem 2" text to be replaced .
any ideas for an automated way to do this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried Multiple Search and Replace and it does what I need, however I hit a snag. The replacing text may have character(s) that the program doesn't like, particularly a leading apostrophe - ' -. I've contacted their support to see if they have a solution.
ASKER
I found out with help from Multiple Search and Replace software author that the problem replacing is because of the VLOOKUP function. I can't replace a text string with the VLOOKUP text (a function) because Excel immediatley looks to the 2nd spreadsheet referenced by that function- I dont have that 2nd spreadsheet here so Excel throws an error. I expect it will work fine at the company site.
ASKER
SSupreme's answer seems to be the best way to go. Aikimark - not for me, but maybe a solution for some with the right skills.
ASKER
aikimark: I'm not quite sure what you mean with your suggestion. If you're suggesting actually writing software, then that's well beyond my ability at the moment. I was looking for either a way to do it within Excel or a piece of software that can do it.