Advanced Replace globally in all worksheets

[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

 

[Guest]

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.