Trim in VBA

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
 
H

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top