Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Function to Remove Extra Spaces in a String (WD 2000 Sr-1)

    Hello,

    I am looking for a function that will strip out extra spaces in group of words. I am collecting name and address information with a userform and I want to make sure that, if the user types extra spaces between the names, the name is displayed correctly on the final document.

    Example1: If the user types "Joe_____Blow" it should appear as "Joe Blow"
    Example 2: user types "Mr._____and Mrs. Larry_____Tate___III" should appear as "Mr. and Mrs. Larry Tate III"
    Note: The underscore marks represent blank spaces.

    If anyone has a function or some code that can accomplish this, it will be greatly appreciated.

    Thanks,

    Mark

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to Remove Extra Spaces in a String (WD 2000 Sr-1)

    Hi Mark:

    I'm not a VBA expert so I can't devise a function for you, but you can use Find/Replace to strip out extra spaces:
    Using Wildcards:
    Find: <space>{2,}
    Replace: <space> where <space> is just an ordinary space that you insert by pressing the spacebar. This will find all "2 or more spaces" & replace it with one space.

    You could also do it without wildcards:
    Find: ^w
    Replace: <space>

    Hope this helps.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Oregon, USA
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function to Remove Extra Spaces in a String (WD 2000 Sr-1)

    Hi Mark,

    The version of VBA in Work 2k added the Replace function. The following replaces all instances of two spaces with one. You may need to iterate through it several times if you're concerned about three or four spaces in succession.

    <pre>Dim str As String

    str = "Bob Jones"
    str = Replace(str, " ", " ")
    </pre>


    HTH,

    Chris

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Function to Remove Extra Spaces in a String (WD 2000 Sr-1)

    Replace is cool, but here's something even more amazing:

    <pre>Public Function WildReplace(strExpression As String, strFind As String, _
    strReplace As String, Optional bolReplaceAll As Boolean = True, _
    Optional bolCaseSensitive As Boolean = False) As String
    'requires VBScript 5 = IE 5.x
    'perform minimal parameter checking
    If (strExpression = vbNullString) Or (strFind = vbNullString) Then
    WildReplace = strExpression
    Exit Function
    End If
    Dim objRegExp As Object
    Set objRegExp = CreateObject("vbscript.regexp")
    objRegExp.IgnoreCase = Not bolCaseSensitive
    objRegExp.Global = bolReplaceAll
    objRegExp.Pattern = strFind
    WildReplace = objRegExp.Replace(strExpression, strReplace)
    End Function</pre>

    As an example:

    <pre>Sub TestReplace()
    Dim strInput As String
    strInput = "There is

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •