# 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?

edit retag close merge delete

Sort by » oldest newest most voted

Hello,

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: 6.3.2.2, 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.

more

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) ;-)

( 2019-09-27 08:39:07 +0200 )edit

@Mike Kaganski: 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.

( 2019-09-27 14:04:25 +0200 )edit

This worked perfectly thank you!!

( 2019-09-27 16:04:49 +0200 )edit

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.
more