Advanced Replace globally in all worksheets

  • Thread starter Thread starter Nivesh
  • Start date Start date
N

Nivesh

Hi All,

I'm using MS Excel 2000, in which I'd want to change
(replace) a string,globally, in all the worksheets a
workbook contains.
A text string "TEST" that appears in all the worksheets in
the excel file, needs to be replaced with "TEST1", how
would I do it.
I tried using the available functions substitute and
replace, which aren't of much help.

I also tried selecting all worksheets and using "Ctrl H",
but I get the following error:-

"Formula is too long"

Help is much appreciated.

Thanks in advance

Regards
-Nivesh
 
The search & replace with all the worksheets selected should work. The problem you have is that you have a very long formula somewhere (or perhaps many of them). Either, changing "TEST" to "TEST1" in this formula is making it too long or, more likely, the formula contains external references (to other workbooks) that are not open, and the inclusion of the full path to these workbooks is taking them over the limit for editing.

You could try opening all the linked workbooks before running the search/replace on a selection of worksheets and see if that works.
 
Back
Top