Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to check and batch replace text within a formula in an Excel spreadsheet possibly hundreds of files

Posted on 2016-11-10
6
Medium Priority
?
84 Views
Last Modified: 2016-11-23
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?
0
Comment
Question by:RickNCN
  • 4
6 Comments
 
LVL 16

Accepted Solution

by:
SSupreme earned 1800 total points
ID: 41883030
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 200 total points
ID: 41883860
You can also write your own version of the 4dots software, iterating the .xls* files, each worksheet within each workbook, and the formula cells.

You can use the specialcells collection to get to the formula cells faster.  If you make any changes to a formula, you will need to save the workbook before closing it.
0
 

Author Comment

by:RickNCN
ID: 41886866
Ok, I like the look of the 4dots software. I installed it. I'll try it shortly here.


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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:RickNCN
ID: 41887223
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.
0
 

Author Comment

by:RickNCN
ID: 41899784
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.
0
 

Author Closing Comment

by:RickNCN
ID: 41899787
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.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question