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
70 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 16

Accepted Solution

by:
SSupreme earned 450 total points
ID: 41883030
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 50 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

751 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