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

Topic

New Reply

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

Reply | Quote

Replies

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: {2,}
Replace: where 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:

Hope this helps.

Reply | Quote

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.

Dim str As String

str = "Bob  Jones"
str = Replace(str, "  ", " ")

HTH,

Chris

Reply | Quote

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

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

As an example:

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

Reply | Quote