Google-sheets – How to change many formulas at once in Google Sheets

google sheets

I have many formulas, all that look similar to this:

=Countif('2019'!$C2:$Z8,"2")/7

I would like to change each one, adding the if() command, so that they look like this:

=IF(Countif('2019'!$C2:$Z8,"2") = 0, "", Countif('2019'!$C2:$Z8, "2")/7)

The problem is, even if I were to find & replace in a step by step process, each cell is referencing a different part of the data, so I would have to go in manually afterwards and fix them.

What is the easiest way to do this? I could also keep the old formula if there is a better way to replace all 0s with empty cells. All of the 0s are messing up my graphs.

Best Answer

If I understand your question, I think a regular expression search might do the trick. In the replace dialog, check the box for regular expression and search within formulas.

Try searching for: (Count.\*)\\).*

And replace with: if($1 = 0), "", $1)/7)

I think that should do it.