parahumanoid
New member
Hi.
Is there a way I can find fields whose formulas contain a substring term_1 and replace this substring with term_2?
For example, formula is ='[Book1.xlsx]Sheet1'!A1 and there are many such formulas that contain a substring Sheet1 (not necessarily in Book1.xlsx, there may be references to other books, but with same sheet name). What I want is to find all occurrences of Sheet1 and replace them with Sheet43, so the resulting formula is ='[Book1.xlsx]Sheet43'!A1
Whether it's macros based or manual search and replace doesn't matter to me. I can see that the regular search works on formulas, but the replace part of it is something I don't know how to handle.
Any ideas, gurus?
Thanks a bunch.
Alex.
Is there a way I can find fields whose formulas contain a substring term_1 and replace this substring with term_2?
For example, formula is ='[Book1.xlsx]Sheet1'!A1 and there are many such formulas that contain a substring Sheet1 (not necessarily in Book1.xlsx, there may be references to other books, but with same sheet name). What I want is to find all occurrences of Sheet1 and replace them with Sheet43, so the resulting formula is ='[Book1.xlsx]Sheet43'!A1
Whether it's macros based or manual search and replace doesn't matter to me. I can see that the regular search works on formulas, but the replace part of it is something I don't know how to handle.
Any ideas, gurus?
Thanks a bunch.
Alex.