Trim in VBA

  • Thread starter Thread starter Otto Moehrbach
  • Start date Start date
O

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
 
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
 
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
 
Back
Top