How can I quickly add MROUND to a bunch of cells?

I’m using a weightlifting spreadsheet that calculates progression based on percentages. The only problem is that none of the formulas were set to round to 5, which is a necessity. It would take me forever and a day to change every formula to include MROUND(original formula, 5).

I’ve read the Regular Expressions Find and Replace help page, but it’s over my head.

Is there a simple way I can get this done?


select the cells you want to change and got to use Edit -> Find & Replace

Set field Find to: ^(=)(.*)
Set field Replace to: $1MROUND($2,5)
Set Other options Current selection only
Set Other options Regular Expression
Set Search in to Formula

Finally click button Replace All

Note Notation using US locale (-> function parameter delimiter is comma [,])

Tested using:

Version:, Build ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kde5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded

Hope that helps.

Note Notation using US locale (-> function parameter delimiter is comma [,])

Using a universally accepted ; might possibly be better and more universal suggestion?

A nitpick: it’s unnecessary to capture a known character and then replace with its reference - it’s easier to replace ^=(.*) with =MROUND($1;5) :wink:

@mikekaganski: Isn’t a nitpick - You are absolutely right. It was too late for me, and I even don’t have any clue why I fought with that some time: Related to ; - that’s new for me. Thanks and of course it’s the better suggestion.

This worked perfectly thank you!!

Modifying formulas by regex carries potential for error, but in this case the regex substitution suggested by Opaque should work.

If your formulas are part of a tabular setup (and essentially identical), the usual way to deal with this is copy-paste:

  • Edit one formula and check that it works as intended.
  • When satisfied, copy that cell and paste into the other cells where it is needed.