none
Using Replace function to remove white space, why isn't this working?

    Question

  • Hello,

    I created this function to remove white space(s) from strings(text).

    Example: I want to make sure that there is only one space between words regardless of how many spaces a person enters.

    So if there are 10 spaces between words; it will cut it to one. I know this sounds unusual but I am just curious and for the sake of knowledge.

    Like this:

    Public Function TrmSpace(RemoveSpace As String) As String
    RemoveSpace = Replace(RemoveSpace, "    ", " ")
    RemoveSpace = Replace(RemoveSpace, "   ", " ")
    RemoveSpace = Replace(RemoveSpace, "  ", " ")
    TrmSpace = RemoveSpace
    End Function
    

    The spaces seem to be inconsistent, is there any other way I can do this?

     

    Thanks everyone

    Wednesday, December 21, 2011 4:01 AM

Answers

  • Another function will be, like this below, it will work only for Text fields:

     

    Public Function TrimSpaces(strString As String) As String
        'Returns a string minus additional spaces
        Dim strLetter As String, strNew As String
        Dim i As Integer, intScount As Integer
        For i = 1 To Len(strString)
            'Loop through the text and get rid of any surplus spaces
            strLetter = Right(Left(strString, i), 1)
            If strLetter = " " Then
                intScount = intScount + 1
            Else
                intScount = 0
            End If
            'Check if less than two spaces, if so, add letter
            If intScount < 2 Then strNew = strNew & strLetter
        Next
        TrimSpaces = Trim(strNew)
    End Function
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by Synthologic Thursday, December 22, 2011 1:34 AM
    Wednesday, December 21, 2011 8:23 PM
    Moderator

All replies

  • hi,
     
    try with the Trim excel function
     
    RemoveSpace = Trim(TheText)
     --
    isabelle
     
    Le 2011-12-20 23:01, Synthologic a écrit :
    > Hello,
    >
    > I created this function to remove white space(s) from strings(text).
    >
    > Example: I want to make sure that there is only one space between words regardless of how many spaces a person enters.
    >
    > So if there are 10 spaces between words; it will cut it to one. I know this sounds unusual but I am just curious and for the sake of knowledge.
    >
    > Like this:
    >
    > Public  Function  TrmSpace(RemoveSpaceAs  String)As  String
    > RemoveSpace =Replace(RemoveSpace,"     ","  ")
    > RemoveSpace =Replace(RemoveSpace,"    ","  ")
    > RemoveSpace =Replace(RemoveSpace,"   ","  ")
    > TrmSpace = RemoveSpace
    > End  Function
    >
    > The spaces seem to be inconsistent, is there any other way I can do this?
    >
    > Thanks everyone
    >
     
    Wednesday, December 21, 2011 5:03 AM
  • hi,
     
    try with the Trim excel function
     
    RemoveSpace = Trim(TheText)
     --
    isabelle
     
    Le 2011-12-20 23:01, Synthologic a écrit :
    > Hello,
    >
    > I created this function to remove white space(s) from strings(text).
    >
    > Example: I want to make sure that there is only one space between words regardless of how many spaces a person enters.
    >
    > So if there are 10 spaces between words; it will cut it to one. I know this sounds unusual but I am just curious and for the sake of knowledge.
    >
    > Like this:
    >
    > Public  Function  TrmSpace(RemoveSpaceAs  String)As  String
    > RemoveSpace =Replace(RemoveSpace,"     ","  ")
    > RemoveSpace =Replace(RemoveSpace,"    ","  ")
    > RemoveSpace =Replace(RemoveSpace,"   ","  ")
    > TrmSpace = RemoveSpace
    > End  Function
    >
    > The spaces seem to be inconsistent, is there any other way I can do this?
    >
    > Thanks everyone
    >
     
    I don't want to trim all the space, I want one space to remain on the left and the right.
    Wednesday, December 21, 2011 5:14 AM
  • Public Function TrmSpace(RemoveSpace As String) As String
    Dim i As Long
    For i = 1 To 5
      RemoveSpace = Replace(RemoveSpace, "   ", " ")
      RemoveSpace = Replace(RemoveSpace, "  ", " ")
    Next i
    TrmSpace = RemoveSpace
    End Function

    Greg Maxey Please visit my website at: http://gregmaxey.mvps.org/word_tips.htm
    Wednesday, December 21, 2011 1:15 PM
  • On Wed, 21 Dec 2011 05:14:45 +0000, Synthologic wrote:
     
    >I don't want to trim all the space, I want one space to remain on the left and the right.
     
     
    To ignore the leftmost and rightmost characters:
     
    Function TrimSpace(s As String) As String
        TrimSpace = Left(s, 1) & _
            WorksheetFunction.Trim(Mid(s, 2, Len(s) - 2)) _
            & Right(s, 1)
    End Function
     
    To ensure a space at the left and right:
     
    Function TrimSpace(s As String) As String
        s = " " & WorksheetFunction.Trim(s) & " "
    End Function
     
    Obviously, both of those can also be represented as worksheet formulas.
     

    Ron
    Wednesday, December 21, 2011 1:21 PM
  • Another function will be, like this below, it will work only for Text fields:

     

    Public Function TrimSpaces(strString As String) As String
        'Returns a string minus additional spaces
        Dim strLetter As String, strNew As String
        Dim i As Integer, intScount As Integer
        For i = 1 To Len(strString)
            'Loop through the text and get rid of any surplus spaces
            strLetter = Right(Left(strString, i), 1)
            If strLetter = " " Then
                intScount = intScount + 1
            Else
                intScount = 0
            End If
            'Check if less than two spaces, if so, add letter
            If intScount < 2 Then strNew = strNew & strLetter
        Next
        TrimSpaces = Trim(strNew)
    End Function
    

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by Synthologic Thursday, December 22, 2011 1:34 AM
    Wednesday, December 21, 2011 8:23 PM
    Moderator
  • And here's another method using regular expressions:

    =============================
    Function TrimInternalSpaces(s As String)
      Dim re As Object
    Set re = CreateObject("vbscript.regexp")
    With re
        .Pattern = "( ){2,}"  'single space within the ( )
        .Global = True
    End With
    TrimInternalSpaces = re.Replace(s, " ") 'single space within the " "

    End Function
    ==========================


    Ron
    Wednesday, December 21, 2011 8:49 PM
  • Couldn't resist(vba):

    Function news(s2)
    Do
    s2 = Replace(s2, "  ", " ")
    n = InStr(1, s2, "  ")
    Loop Until n = 0
    news = s2
    End Function

     

     

     


    • Edited by shockley Wednesday, December 21, 2011 9:58 PM
    Wednesday, December 21, 2011 9:57 PM
  • TRIM function, Removes all spaces from text except for single spaces between words.
     
    --
    isabelle
     
    Le 2011-12-21 00:14, Synthologic a écrit :
    > I don't want to trim all the space, I want one space to remain on the left and the right.
     
    Wednesday, December 21, 2011 10:06 PM
  • WOW! What a rush of great resources and knowledge.

     

    You guys all rock!

     

    Thanks everyone!

    Thursday, December 22, 2011 1:33 AM