Trying to get rid of un-wanted spaces in name

[drakuz1965]

Here is what I am trying to accomplish:

Example: Doe, J ohn Lynn
Wanted outcome: Doe, John Lynn

For the most part this is working fine but I can't get the middle name to work in all scenarios. Sometimes the last name will not have a space in the name. Need help with a better way of doing this. Here is my code. "Tab" holds the value of the persons name.
-----------------------------------------------------------
Dim currentStr, newStr, lName, fName, mName

newStr = Tab

newStr = Split(newStr, ",")

lName = newStr(0)
fName = newStr(1)

mName = Split(fName, " ")

lName = DeleteChars(newStr(0), " ")
fName = DeleteChars(newStr(1), " ")

Tab = lName & ", " & mName(1) & mName(2) & " " & mName(3)

----------------------------------------------------

 

[DPlank]

Raises a couple questions:

First what will you do if there is no middle name?

Second have you looked at the Trim(), Mid() and Replace() functions? These will ensure your string manipulations are relatively painless...

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[mharroff]

What about a simple loop thru each character of your string looking to see if the space is followed by an uppercase letter. If it is then leave the space else delete it.

Similar to the following:

For p = 1 To Len(tab)
    If Mid(tab, p, 1) = " " Then
            If Mid(tab, p + 1,1) >= "A" And Mid(tab, p + 1, 1) <= "Z" Then
                    newTab = newTab & Mid(tab, p, 1)
            End If
        Else
            newTab = newTab & Mid(tab, p, 1)
    End If
Next

 

[strongm]

Using mharroff's logic with regular expressions:

[blue]With New RegExp 
    .Pattern = "(\s)([a-z])"
    Tab = .Replace(Tab, "$2")
End With[/blue]

 

[strongm]

Actually, that would be fractionally better as:

[blue]With New RegExp
    .Pattern = "(\s)([a-z])"
    [b].Global = True[/b]
    Tab = .Replace(Tab, "$2")
End With[/blue]