Find and Replacing Characters in a Formula

Topic

New Reply

Is there an easy way to replace a cell reference in different formulae for another cell reference.

ie replace cell reference c1 with cd1 in 10 different formulae in a worksheet.

I have tried find and replace but when the formula contains the sum function and the cell reference was su* it replaced the values and made the formula invalid

for example sum(s1+b1+c1) to be (d1+b1+c1) and

Reply | Quote

Replies

For that kind of find and replace, you’ll need to do it in 3 steps.
First replace sum with another nonsense string – you know something like ##$$!!
Then do your replacement.
Then change ##$$!! back to sum.

Reply | Quote

or, depending on your formula, include some of the characters adjacent to the target to make the search string more unique, such as:

Search charcters: +su
Replace characters: +du

won’t trash sum() formulas unless they read =…+sum()

Be creative with adjacent characters.

Reply | Quote