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
41 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 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

17 Experts available now in Live!

Get 1:1 Help Now