SS,

The procedures below runs off of the selection. In AddFunciton, if a cell
in the selection has a formula, then the TRIM(CLEAN( will be added to the
existing formula and the new formula will be placed in the cell. In
TrimCleanSource, the assumption is that the source data is the true source of
a hard coded cell, i.e. no formulas.

Best,

Matthew Herbert

Sub AddFunction()
Dim rngCell As Range
Dim strText As String

Application.ScreenUpdating = False
For Each rngCell In Selection.Cells
If rngCell.HasFormula Then
With rngCell
'get all of the formula except for the "="
strText = Right(.Formula, Len(.Formula) - 1)
'wrap the new functions into the existing formula
strText = "=Trim(Clean(" & strText & "))"
'insert the new formula
.Formula = strText
End With
End If
Next rngCell

End Sub

Sub TrimCleanSource()
Dim rngCell As Range

Application.ScreenUpdating = False
For Each rngCell In Selection.Cells
With rngCell
.Value = Trim(WorksheetFunction.Clean(.Value))
End With
Next rngCell

End Sub
"SANDIND" wrote:

Hi, I have more than 1000 formulaes in one sheet and to all those formulaes I
want to add Trim and Clean function. Is there anyways to add these two
functions to all the formulaes.

Also if there is any option to use a VB code that would trim and clean all
the selected data in spreadshet , this way i could avoid adding those to
formulaes already in sheet.

Thanks for help.
--
SS