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
74 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 46

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

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;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

632 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