How do I replace the formula in a cell with the output of the formula?

Question

How do I replace the formula in a cell with the output of the formula?

I simply need "=RIGHT(E86,LEN(E86)+1-FIND("(",E86,1))" to become "(e)"

Is there a way to do this to the whole sheet? Replace all the cells with formulas with the text they are displaying? I am using version 2003.

Answer 1

Select the cells you want to replace and copy them. Then go to "Edit->Paste Special" and select values instead of all.

Since this doesn't change non-formula cells, you could select the entire sheet and copy->paste special to remove all formulas.

To do it programatically, look at Steve's answer. He's got the code you'll need.

Answer 2

Another way to do it: double-click the cell in question and press F9.

Answer 3

Alternatively something like the following will work if you want to avoid using the clipboard

Dim r as range
For each r in Worksheets("Sheet1").UsedRange.Cells
   r.Value = r.Value
Next

I haven't excel to hand I'm afraid so you'll need to check the syntax.