Mass replace cells with changing only specific data from their formula

Question

I have a sheet with cells that contain formulas like this:

=IF(LEN('API Tool'!P358),IF('API Tool'!P358/POWER(10,18)>=C3+(C3*25/100),"API ERROR",'API Tool'!P358/POWER(10,18)),)

And I would like to mass replace them all and add Indirect function to C cell references like this (check the bold letters):

=IF(LEN('API Tool'!P358),IF('API Tool'!P358/POWER(10,18)>=INDIRECT("$C3")+(INDIRECT("$C3") *25/100),"API ERROR",'API Tool'!P159/POWER(10,18)),)

Is there any script or regular expression that i could use in order to mass replace all?

Thanks

Answer 1

First, using a "$" symbol with INDIRECT is redundant and, in this case, is also just complicating matters. Every part of every INDIRECT reference enclosed between quotation marks is a locked reference by nature.

That said, if the changes you want to make are in a specific range only, select that range first. If they are in an entire sheet or across the entire spreadsheet, move to the next step.

Hit Ctrl-H (or Cmd-H or Edit > Find and replace) to bring up the "Find and Replace" dialog box.

In the "Find" field, enter this:

([^A-Z])(C\d+)

In the "Replace with" field, enter this:

$1INDIRECT("$2")

Make sure that you set the "Search" location to match your desired scope. (If you selected a specific range and then choose "Specific range" here, the range you currently have selected will populate.)

Check the checkboxes for "Search using regular expressions" and "Also search within formulas." This will automatically also select "Match case" (since regular expressions are case-sensitive).

Click the "Replace all" button.

The REGEX has to include (and avoid) cases where a column may incidentally include C, such as a reference to Column AC, which is why it has two parts: one that makes sure the character preceding the C is not another valid column letter and one that grabs the direct part in question.

In addition, while this is not part of what you asked, I caution you to think through why you are replacing those direct references with INDIRECT ones. It's hard to imagine a case where that would be necessary. And once you change them to INDIRECT, if you add or delete columns or rows anywhere, those references will not adjust relative to those changes — which means every one of those references will then point to the wrong place thereafter.