Cleaning Text

[James]

Hi All

Is it possible to Clean up the whole sheet at once.
i.e Apply TRIM & CLEAN function to all cells to get rid of
obious mess.
Also to Clean are internal spaces i.e Some cells might
have more then one space beween the values
e.g "Joe Doe Bill" to make it "Joe Doe Bill" with
one space only

Thanks you guys

James

 

[Tom Ogilvy]

No, you have to loop

Dim cell as Range
On Error Resume Next
for each cell in
Activesheet.UsedRange.specialCells(xlconstants,xlTextValues)
cell.Value = Application.Clean(application.Trim(cell))
Next
On Error goto 0