Deleting part of a formula

[Hoosier03]

Is there a way to delete part of a formula without deleting the entire formula?

I have these formulas =(+U21*OpsCalc!$E$161)*0.01, =(U21*OpsCalc!$E$162)*0.01,=(U21*OpsCalc!$E$163)*0.01, etc. and I have to delete the *.01 out of them. There are 250 in each worksheet and I have 17 worksheets. Is there a way to do this without deleting all of them manually?


I tried find and replace and I could not get it to delete just part of the formula. I am using Excel 2007.


Thanks,


Rodney

 

[Rick Rothstein]

Select you cells, press CTRL+H to bring up Excel's Replace Dialog box, put this (exactly as shown)...

~*0.01

in the "Find what" field, leave the "Replace with" field empty, click the "Options>>" button to reveal all the options and make sure the "Match entire cell contents" checkbox is not checked, then click the "Replace All" button.

 

[jim may]

Paste into a Standard Module - Highlight the area of your spreadsheet with the formulas, then run FixFormulas -- Do this on a FILE BACKUP First...

Sub FixFormula()
Dim C As Range
Dim Temp As String
For Each C In Selection
If C.HasFormula Then
Temp = C.Formula
Temp = WorksheetFunction.Substitute(Temp, "*0.01", "")
C.Formula = Temp
End If
Next C
End Sub

 

[Hoosier03]

That worked Rick thanks.

 

[Hoosier03]

Thanks for your post Jim. I tried what Rick recommended first and it worked.