Trim in vba

[lezawang]

Hi
Trim (VBA function) is not working here. The output message I go is " hello ................ there"
Anything wrong with the code below? Thank you so much

Sub mytrim()
    Dim s As String
    s = Trim("               hello                             there   ")
    MsgBox s
End Sub

 

[steve the fish]

Use WorksheetFunction.Trim or Application.Trim

 

[Fluff]

The VBA trim function only removes leading/trailing spaces.

 

[CalcSux78]

Sub MyTrim()
Dim s$, i&
Dim Arr As Variant
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")


s = Trim("               hello                             there   ")
Arr = Split(s, " ")
For i = LBound(Arr) To UBound(Arr)
    If Not Arr(i) = "" Then dict(Arr(i)) = dict(Arr(i)) + 1
Next i
ReDim Arr(1 To dict.Count)
For i = 1 To dict.Count
    Arr(i) = dict.Keys()(i - 1)
Next i
s = Join(Arr, " ")
MsgBox s
End Sub

 

[Fluff]

Or simply

s = Application.Trim("               hello                             there   ")

 

[CalcSux78]

If you want your string to result in "hello there", use Fluff's method.

If you want the string to be "hello there" then post #4 would be a better fit.

 

[Fluff]

They look tome to be exactly the same

Also if the string was

"    hello           hello                             there   "

your code would result in "hello there" rather than "hello hello there"

 

[steve the fish]

To be fair they look the same to me too. Is there meant to be a difference?

 

[RoryA]

FWIW, I already told you this in your other thread: https://www.mrexcel.com/forum/excel-questions/1096207-vba-functions-not-under-application-dot.html

 

[CalcSux78]

To be fair they look the same to me too. Is there meant to be a difference?

I didn't notice until it was too late to correct. I thought the OP was unclear as to the desired output. Are we looking to remove all of the additional spaces? Are we just looking to remove the leading and trailing spaces?

My attempt was to remove all of the spaces to leave all of the words separated by a single space. By using a collection instead of a dictionary approach, this is possible.

Sub MyTrim()

Dim s$, i&
Dim Arr As Variant
Dim Coll As New Collection


s = Trim("     hello          hello                             there   ")
Arr = Split(s, " ")
For i = LBound(Arr) To UBound(Arr)
    If Not Arr(i) = "" Then Coll.Add Arr(i)
Next i
ReDim Arr(1 To Coll.Count)
For i = 1 To Coll.Count
    Arr(i) = Coll(i)
Next i
s = Join(Arr, " ")
MsgBox s
End Sub