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
54 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 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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…

896 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now