How to remove spaces in between text?

Question

Why trim is not working in VBA?

for i = 3 to 2000
activesheet.cells(i,"C").value = trim(Activesheet.cells(i,"C").value)
next i

It is unable to remove the spaces in between the text.

 hiii              how ' even after trying trim the o/p is still these
 hiii              how

I need to remove the extra spaces so I found Trim to do it but it is not working while ltrim and rtrim are.

Answer 1

The VBA Trim function is different than Excel's. Use Excel's Application.WorksheetFunction.Trim function instead.

Excel Trim will remove all spaces except a single space between words. VBA Trim will remove leading and trailing spaces.

Thank MS for using the same keyword for different functions.

Answer 2

Trim removes extra spaces at start and end, not in the middle of a string.

Function CleanSpace(ByVal strIn As String) As String
    strIn = Trim(strIn)

  ' // Replace all double space pairings with single spaces
    Do While InStr(strIn, "  ")
        strIn = Replace(strIn, "  ", " ")
    Loop

    CleanSpace = strIn
End Function

From here.

PS. It's not the most efficient way to remove spaces. I wouldn't use on many, very long strings or in a tight loop. It might be suitable for your situation.