Trim in VBA

[Otto Moehrbach]

Excel 2002, WinXP
The worksheet Trim function removes all trailing and leading spaces and the
more-than-one spaces between words. The VBA Trim command does not address
the spaces between words.
Is there a variation of the Trim command that does address the excess
spaces between words or do I have to use the worksheet function in VBA to do
that? Thanks for your help. Otto

 

[Harald Staff]

Hi Otto

I think the worksheet function is the best way to do it:

Sub test()
Dim s As String
s = " Hi There Otto "
s = Application.Trim(s)
MsgBox "*" & s & "*"
End Sub

HTH. best wishes Harald

 

[Tom Ogilvy]

Only the worksheetfunction TRIM does that.

 

[Gord Dibben]

Otto

Worksheet Function only.

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

Gord Dibben Excel MVP

 

[Otto Moehrbach]

Harald, Tom, & Gord
Thanks for your help. Otto