How to remove spaces in between text?

Why trim is not working in VBA?

for i = 3 to 2000
activeshee开发者_JAVA百科t.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.

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.

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.

I know this question is old but I just found it and thought I'd add what I use to remove multiple spaces in VBA....

cleanString = Replace(Replace(Replace(Trim(cleanString), _ 
 " ", " |"), "| ", ""), " |", " ") 'reduce multiple spaces chr(32) to one

When you call Trim() VBA is actually calling Strings.Trim(). This function will only remove leading and trailing spaces. To remove excessive spaces within a string, use

Application.Trim()

Are all your other functions leaving whitespace behind?

Get CleanUltra!

CleanUltra removes all whitespace and non-printable characters including whitespace left behind by other functions!

I hope you find this useful. Any improvements are welcome!

Function CleanUltra( _
       ByVal stringToClean As String, _
       Optional ByVal removeSpacesBetweenWords As Boolean = False) _
        As String
' Removes non-printable characters and whitespace from a string


' Remove the 1 character vbNullChar. This must be done first
'  if the string contains vbNullChar
    stringToClean = Replace(stringToClean, vbNullChar, vbNullString)

    ' Remove non-printable characters.
    stringToClean = Application.Clean(stringToClean)

    ' Remove all spaces except single spaces between words
    stringToClean = Application.Trim(stringToClean)

    If removeSpacesBetweenWords = True Then _
       stringToClean = Replace(stringToClean, " ", vbNullString)

    CleanUltra = stringToClean
End Function

Here's an example of it's usage:

Sub Example()
    Dim myVar As String
    myVar = " abc d e  "

    MsgBox CleanUltra(myVar)
End Sub

Here's a test I ran to verify that the function actually removed all whitespace. vbNullChar was particularly devious. I had to set the function to remove it first, before the CLEAN and TRIM functions were used to stop them from removing all characters after the vbNullChar.

Sub Example()
    Dim whitespaceSample As String
    Dim myVar As String

' Examples of various types of whitespace
'  (vbNullChar is particularly devious!)
    whitespaceSample = vbNewLine & _
                       vbCrLf & _
                       vbVerticalTab & _
                       vbFormFeed & _
                       vbCr & _
                       vbLf & _
                       vbNullChar

    myVar = "     1234" & _
            whitespaceSample & _
            "     56      " & _
            "789     "

    Debug.Print "ORIGINAL"
    Debug.Print myVar
    Debug.Print "Character Count: " & Len(myVar)


    Debug.Print
    Debug.Print "CLEANED, Option FALSE"


    Debug.Print CleanUltra(myVar)
    Debug.Print CleanUltra(myVar, False)
    '   Both of these perform the same action.  If the optional parameter to
    '   remove spaces between words is left blank it defaults to FALSE.
    '   Whitespace is removed but spaces between words are preserved.
    Debug.Print "Character Count: " & Len(CleanUltra(myVar))


    Debug.Print
    Debug.Print "CLEANED, Option TRUE"

    Debug.Print CleanUltra(myVar, True)
    '   Optional parameter to remove spaces between words is set to TRUE.
    '   Whitespace and all spaces between words are removed.
    Debug.Print "Character Count: " & Len(CleanUltra(myVar, True))

End Sub

My related issue was that the last character was a chr(160) - a non-breaking space. So trim(replace(Str,chr(160),"")) was the solution.

I know this question is old but I just want to share my solution on how to deal and fix with this issue.

Maybe you might wondering why sometimes TRIM function isn't working, remember that it will only remove spaces and spaces are equivalent to ASCII 32. So if these ASCII 13 or ASCII 10 exists in the Beginning or end of your string value then TRIM function will not work on it.

Function checkASCIItoBeRemoved(myVal) As String

Dim temp As String
temp = Replace(Trim(myVal), Chr(10), Empty)
temp = Replace(temp, Chr(13), Empty)
checkASCIItoBeRemoved = temp

End Function

With this code it works for me, by the way if this might not work on your side then try to check the ASCII of you string value because it might have another invisible special char that might not covered on my code to replace on it, kindly add on it to work.
Please see reference for some invisible special char.

I know this is quite old but thought I'd add in something else rather than all these replace options.

Using trim (or trim$) in VBA will remove the leading and trailing spaces, which as mentioned is different from =TRIM in Excel.

If you need to remove spaces (as mentioned below not necessarily all whitespace) from inside a string simply use WorksheetFunction.Trim.

Sometimes what looks to be a space is not a space but a character that cannot be displayed. Use the ASC function to get the integer value of the character. Then use the following code:

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

  ' Searches string from end and trims off excess ascii characters

  Dim StrLength As Integer
  Dim SingleChar As Integer
  Dim StrPosition As Integer

  SingleChar = 1

  StrLength = Len(StrIn)
  StrPosition = StrLength - 1

    Do Until Asc(Mid(StrIn, StrPosition, SingleChar)) <> 0
        StrPosition = StrPosition - 1
    Loop
  StrIn = Mid(StrIn, 1, StrPosition)

End Function

If You are familiar with collections, i once wrote a quick code that process the whole sheet even if it is huge and remove all double spaces, lead and trail spaces and invisible characters from all cells. Just take care it will remove the format of your text, i also did not do much testing and it's exhaustive but it worked for my short task and worked fast.

This is an Auxiliary function that loads the sheet into a collection

Function LoadInCol() As Collection

Dim currColl As Collection
Dim currColl2 As Collection

Set currColl = New Collection
Set currColl2 = New Collection

With ActiveSheet.UsedRange
    LastCol = .Columns(.Columns.Count).Column
    lastrow = .Rows(.Rows.Count).Row
End With

For i = 1 To lastrow

    For j = 1 To LastCol
        currColl.Add Cells(i, j).Value
    Next

    currColl2.Add currColl
    Set currColl = New Collection

Next

    Set LoadInCol = currColl2

End Function

And this is the main Sub that removes the spaces

Sub RemoveDSpaces()

'Removes double spaces from the whole sheet

Dim Col1 As Collection
Dim Col2 As Collection
Dim Col3 As Collection

Dim StrIn As String

Dim Count As Long

Set Col1 = New Collection
Set Col2 = New Collection
Set Col3 = New Collection


Set Col1 = LoadInCol()

Count = Col1.Count

i = 0

For Each Item In Col1

i = i + 1
If i >= Count + 1 Then Exit For

    Set Col2 = Item

    For Each Item2 In Col2

        StrIn = WorksheetFunction.Clean(Trim(Item2))

        Do Until InStr(1, StrIn, "  ", vbBinaryCompare) = 0
            StrIn = Replace(StrIn, "  ", Chr(32))
        Loop

        Col3.Add StrIn

    Next

Col1.Remove (1)
Col1.Add Col3
Set Col3 = New Collection

Next

'Store Results

Cells.ClearContents

    Z = 1
    m = 1
    Set Col3 = New Collection

    For Each Item In Col1

        Set Col3 = Item

            For Each Item2 In Col3
                Cells(Z, m) = Item2
                m = m + 1
            Next

            m = 1
            Z = Z + 1

    Next

End Sub