Find and Replace ROUNDUP(stuff, 0)

[emkramer]

I have several hundred cells with =ROUNDUP(formulas, 0). I want to
erase the ROUNDUP( ,0), but don't know how to use find and replace on
this. I can't do it in two steps, because then Excel doesn't like the
resulting formula after deleting either ROUNDUP( or ,0). Is there a
simple way to turn off formulas? Or another way around this problem?

Earl

 

[Guest]

Copy the range, paste special, values.

Dave

 

[emkramer]

Unfortunately, I need to keep the formulas inside of the ROUNDUP
function. I just need to delete "ROUNDUP(" and ",0)".

Thanks,

Earl

 

[Guest]

Sorry I misunderstood your question.

One possible way:

1) Create a UDF, getformulaI which returns the formula in a cell.
2) Format the returned formulas as text
3) Run the MID function to extract from the text string the formula you want
4) Convert the text string to a formula.

Code for the UDF is:

Function GetFormulaI(Cell As Range) As String
'Application.Volatile = True
If VarType(Cell) = 8 And Not Cell.HasFormula Then
GetFormulaI = "'" & Cell.Formula
Else
GetFormulaI = Cell.Formula
End If
If Cell.HasArray Then _
GetFormulaI = "{" & Cell.Formula & "}"
End Function

This is rather awkward tho. There may be a different way