ROUND function

  • Thread starter Thread starter Robin W.
  • Start date Start date
R

Robin W.

I use a worksheet in which I take the client's numbers and
place them in my worksheet by linking theie worksheet in
the formula. I should have added the ROUND function with
0 decimal places to the formula but didn't. Is there any
way to add this across a range of cells?

Thanks
 
Assuming you have something like =xxx in each cell

Put a 1 in a blank cell, select all your data and do Edit / Paste Special / Multiply (Tick Paste
Formulas) and hit OK. Your data will now all look like:-

=(xxx)*1

Select all your data and do Edit / Replace, replacing =( with %%=ROUND(

Then do Edit / Replace replacing )*1 with ,0)

Then do Edit / replace, replacing %% with blank (leave box empty)
 
This is a multi step process.
1) Convert your formula to text by searching for the = and
replacing with '=
2) Use the Mid function to eliminate the =
3) Use the Concatenate function to join an =; round(; your
formula from 2 above; and ,2) to complete the rounded
formula. Note the ; is only used to separate the elements
of the Concatenate function.
4) Convert the above from text to a formula by using the
text to columns function.
 
Back
Top