1

I have a big spreadsheet in google docs with many formulas like this: =MIN(C3;C4)/500+4. Now we need to add $ before every cell row/column to make it absolute. So, the formula should look like this: =MIN($C$3;$C$4)/500+4.

Is it possible to replace all these names in bulk, so I won't need to manually change each of them? Probably, a regexp could be used.

1

It will depend upon what else you have in your spreadsheet. Text like "Minutes(Cardinal)" might require care. First I would have the formulae visible (View, All formulae or Ctrl+`).

Then it rather depends upon the formulae, of which you have only provided a single example. A column as the example that had been copied down could be changed with simple Edit, Find and Replace.... of (C with ($C and ;C with ;$C, with Also search within formulae.

With two columns the same (say Cs and also Ds), Search using regular expressions could be applied to save repeating the above for Ds, with finding:

(\()(.) and  
(\;)(.)  

and in both cases replacing with:

$1$ $2  

Two columns for example, but it could be the whole sheet. Unfortuantely that regex introduces spaces into the formulae, though those can then be removed quite easily and in bulk.

So possible, but a peculiar requirement for which they may be a better alternative solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.