none
Remove all imbedded spaces from string but one RRS feed

  • Question

  • I would like to be able to remove all the spaces from the middle of a string but one.

    so if my string is "abc   123" the final result should just be "abc 123"

    Thanks

    Thursday, January 17, 2019 3:37 PM

Answers

  • If you're doing this in Excel, you can call the worksheet function TRIM in VBA. It will replace multiple spaces with single spaces:

        Dim MyString As String
        MyString = "abc   123"
        MyString = Application.WorksheetFunction.Trim(MyString)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by James N San Thursday, January 17, 2019 5:30 PM
    Thursday, January 17, 2019 3:43 PM
  • If this is not in Excel, you can use a custom function:

    Function TrimSpaces(ByVal s As String) As String
        Do While InStr(s, "  ") > 0
            s = Replace(s, "  ", " ")
        Loop
        TrimSpaces = Trim(s)
    End Function

    Use like this:

        Dim MyString As String
        MyString = "abc   123"
        MyString = TrimSpaces(MyString)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by James N San Thursday, January 17, 2019 5:30 PM
    Thursday, January 17, 2019 3:47 PM

All replies

  • If you're doing this in Excel, you can call the worksheet function TRIM in VBA. It will replace multiple spaces with single spaces:

        Dim MyString As String
        MyString = "abc   123"
        MyString = Application.WorksheetFunction.Trim(MyString)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by James N San Thursday, January 17, 2019 5:30 PM
    Thursday, January 17, 2019 3:43 PM
  • If this is not in Excel, you can use a custom function:

    Function TrimSpaces(ByVal s As String) As String
        Do While InStr(s, "  ") > 0
            s = Replace(s, "  ", " ")
        Loop
        TrimSpaces = Trim(s)
    End Function

    Use like this:

        Dim MyString As String
        MyString = "abc   123"
        MyString = TrimSpaces(MyString)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by James N San Thursday, January 17, 2019 5:30 PM
    Thursday, January 17, 2019 3:47 PM
  • That works great. Thanks.
    Thursday, January 17, 2019 5:30 PM