SOLVED

Avoid Breaking formulas While Searching and Replacing

Copper Contributor

How can I easily search and replace a group of cells containing similar formulas without breaking the formula and getting an error.  Sometimes it's an easy substitution, but other times you have to modify both the beginning and the end of the formula to make the change desired.  However, with Ctrl-H, you can only modify one thing at a time.  So, modifying only the beginning or only the end "breaks" the formula and generates an error.  In Google Sheets, there's an option "Also search within formula", which when selected temporarily displays all formulas like a text string allowing you to make modifications in steps until the change is completed.  While you can modify formulas in Excel the same way, the search and replace still seems to consider it a formula and fails on the first formula applied to.  This is probably something very easy, but I haven't been able to find anything helpful.

2 Replies
best response confirmed by GTS (Copper Contributor)
Solution

@GTS 

Try this.

First replace = with ##.

Then make your custom replaces.

And then replace ## with =.

 

Thank you! Your suggestion worked perfect.
1 best response

Accepted Solutions
best response confirmed by GTS (Copper Contributor)
Solution

@GTS 

Try this.

First replace = with ##.

Then make your custom replaces.

And then replace ## with =.

 

View solution in original post