none
Is there a way to remove ALL extra spaces in a string? RRS feed

  • Question

  • Hi,

    I know there is a LTrim, RTrim, and Trim.  However, this just removes leading / ending extra spaces.  Is there anyway to remove ALL extra spaces in the string?

    Thanks,

    Dennis


    Dennis Solis
    Thursday, November 4, 2010 3:34 AM

Answers

  • You can use the replace function to replace " " with "".
    Jeanette Cunningham (Access MVP) Pakenham, Victoria Australia
    • Marked as answer by DenSolis Thursday, November 4, 2010 4:40 AM
    Thursday, November 4, 2010 4:04 AM
  • Hi Dennis
    You mean you want to remove all spaces in the string? To do so, use the Replace() function:
     
    for example:
    MsgBox replace(" This Is A String ", " ", ""), , "despaced"
     
    if you want to remove obolete whitespace and double spaces you may use a combination of trim() and replace().
    For this task you best create a new public function in a standard module, such as:
     
    Public Function removeObsoleteWhiteSpace _
      (FromString As Variant) As Variant
      If IsNull(FromString) Then 'handle Null values
        removeObsoleteWhiteSpace = Null
        Exit Function
      End If
      Dim strTemp As String
      strTemp = Replace(FromString, vbCr, " ")
      strTemp = Replace(strTemp, vbLf, " ")
      strTemp = Replace(strTemp, vbTab, " ")
      strTemp = Replace(strTemp, vbVerticalTab, " ")
      strTemp = Replace(strTemp, vbBack, " ")
      strTemp = Replace(strTemp, vbNullChar, " ")
      While InStr(strTemp, "  ") > 0
        strTemp = Replace(strTemp, "  ", " ")
      Wend
      strTemp = Trim(strTemp)
      removeObsoleteWhiteSpace = strTemp
    End Function
     
    You then can call this function in your code, expressions or queries, for example:
     
    MsgBox removeobsoletewhitespace(" This    Is " & vbTab & " A " & vbCrLf & "String ") , , "dewhitespaced"
    remark:
    If you allow NULL to be passed to this function and to be returned you you savely can use it in a query that may have NULL in the passed datafields. Else an error may occur or you wouldn't get NULL back if you call it with NULL.
     
    HTH
    Henry

    Hi,

    I know there is a LTrim, RTrim, and Trim.  However, this just removes leading / ending extra spaces.  Is there anyway to remove ALL extra spaces in the string?

    Thanks,

    Dennis


    Dennis Solis
    • Marked as answer by DenSolis Thursday, November 4, 2010 4:40 AM
    Thursday, November 4, 2010 4:21 AM

All replies

  • You can use the replace function to replace " " with "".
    Jeanette Cunningham (Access MVP) Pakenham, Victoria Australia
    • Marked as answer by DenSolis Thursday, November 4, 2010 4:40 AM
    Thursday, November 4, 2010 4:04 AM
  • Hi Dennis
    You mean you want to remove all spaces in the string? To do so, use the Replace() function:
     
    for example:
    MsgBox replace(" This Is A String ", " ", ""), , "despaced"
     
    if you want to remove obolete whitespace and double spaces you may use a combination of trim() and replace().
    For this task you best create a new public function in a standard module, such as:
     
    Public Function removeObsoleteWhiteSpace _
      (FromString As Variant) As Variant
      If IsNull(FromString) Then 'handle Null values
        removeObsoleteWhiteSpace = Null
        Exit Function
      End If
      Dim strTemp As String
      strTemp = Replace(FromString, vbCr, " ")
      strTemp = Replace(strTemp, vbLf, " ")
      strTemp = Replace(strTemp, vbTab, " ")
      strTemp = Replace(strTemp, vbVerticalTab, " ")
      strTemp = Replace(strTemp, vbBack, " ")
      strTemp = Replace(strTemp, vbNullChar, " ")
      While InStr(strTemp, "  ") > 0
        strTemp = Replace(strTemp, "  ", " ")
      Wend
      strTemp = Trim(strTemp)
      removeObsoleteWhiteSpace = strTemp
    End Function
     
    You then can call this function in your code, expressions or queries, for example:
     
    MsgBox removeobsoletewhitespace(" This    Is " & vbTab & " A " & vbCrLf & "String ") , , "dewhitespaced"
    remark:
    If you allow NULL to be passed to this function and to be returned you you savely can use it in a query that may have NULL in the passed datafields. Else an error may occur or you wouldn't get NULL back if you call it with NULL.
     
    HTH
    Henry

    Hi,

    I know there is a LTrim, RTrim, and Trim.  However, this just removes leading / ending extra spaces.  Is there anyway to remove ALL extra spaces in the string?

    Thanks,

    Dennis


    Dennis Solis
    • Marked as answer by DenSolis Thursday, November 4, 2010 4:40 AM
    Thursday, November 4, 2010 4:21 AM
  • If the string may contain multiple-multiple spaces, and no *, you can use something similar to the following:

     

    ? Replace(Replace(Replace("This  is    a string with     spaces", "  ", " *"), "* ", ""), "*", "")
    This is a string with spaces

    Thursday, November 4, 2010 1:20 PM
    Moderator
  • A bit off center perhaps, but if you are working in the MS Office workspace, e.g. Access with a VBA reference to Excel, which I often am, one can use Excel application.worksheetfunction.trim(). Unlike the VBA trim function, this Excel function will also trim consecutive space characters in a string down to 1 space character, in addition to trimming space characters off the front and end of a string.

    phillfri

    Thursday, November 15, 2018 7:18 PM
  • Also, try this.

    Function RemoveMidSpaces(Text As String)
        
        'Uses trim and if logic to check if the last character was a space
        a = Trim(Text)
        
        txtcount = Len(a)
        
        lastltr = ""
        
        For i = 1 To Len(a)
        
            b = Mid(a, i, 1)
            If lastltr = " " Then
                If b <> " " Then
                    c = c & b
                End If
            Else
                c = c & b
            End If
            
            lastltr = b
            
        Next i
        
        If c = 0 Then: c = ""
        
        RemoveMidSpaces = c
        
    End Function

    Wednesday, December 26, 2018 5:44 PM