these are the same as application.worksheetfunction except you dont get
intellisense.

The big advantage of 'forgetting' the worksheetfunction qualifier is
that in case of errors like #NA they will pass the errorvalue as the
function's result where application.worksheetfunction will raise an
error.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Peter Chatterton wrote in message
:

Thanks very much.

Two questions:
How can you say Application.Substitute
without saying WorksheetFunction?

Where are these functions in VBA help?

Thanks again,
Peter.

"keepITcool" wrote in message
...

All the are worksheetfunctions...

the first clean takes care of non printing (incl tabs and linefeeds!)
the subst takes care of the chr(160)
the trim takes care of the double spaces in mid text, and the
starting
and trailing spaces.

Note the &h denotes hexcodes
&h7 = 7 = tab
&ha = 10 = linefeed
&h20 = 32 = space
&hA0 = 160 = non breaking space


Sub cleanstring()
With activecell
'fill with dirt
.Value = "abc" & Chr$(&H20) & Chr$(&H7) & Chr$(&HA) & Chr$(&HA0)
'clean and trim
'always use application iso application.worksheetfunction
'as this has error handling advantages :)
.Value = Application.Trim( _
Application.Substitute( _
Application.Clean(.Value), Chr(&HA0), vbNullString))

End With
End Sub




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Peter Chatterton wrote in message
:

I have a cell that contains X'2020A0' as trailing spaces.
('A0' I am told is a non-breaking space in html)

I've used the following on it:
Application.WorksheetFunction.Clean
RTrim
but can't get them to go away.

Any suggestions would be much appreciated, and thanks,
Peter.

P.S. The data comes from an external source.