Trim in VBA
 

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

Trim in VBA
 

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 Moehrbach" skrev i melding
...
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

Trim in VBA
 

Only the worksheetfunction TRIM does that.

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
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

Trim in VBA
 

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

On Tue, 18 May 2004 07:14:16 -0400, "Otto Moehrbach"
wrote:

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

Trim in VBA
 

Harald, Tom, & Gord
Thanks for your help. Otto
"Otto Moehrbach" wrote in message
...
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