vba convert "lastname, firstname" to "firstname lastname"

[austin350s10]

Does anyone know a good vba function to take a string containing a name formatted as: lastname, firstname and convert it to firstname lastname?

something like:

Sub convertIt()
Dim myName, newName as String
myName = "Smith, Bob"

newName = convertName(myName)

MsgBox = newName
End Sub

Function convertName(passedName As String)
'do stuff
End Function

'output should be "Bob Smith"

 

[austin350s10]

Any ideas would be appreciated....

 

[jproffer]

There we go...try this:

Function FlipNames(MyString As Variant)
Dim lastname As String
Dim firstname As String
Dim name As String
If InStr(1, MyString, ", ") Then
        lastname = Left(MyString, WorksheetFunction.Find(", ", MyString) - 1)
        firstname = Right(MyString, Len(MyString) - WorksheetFunction.Find(", ", MyString) - 1)
name = firstname & " " & lastname
FlipNames = Trim(name)
Else
        firstname = Left(MyString, WorksheetFunction.Find(" ", MyString))
        lastname = Right(MyString, Len(MyString) - WorksheetFunction.Find(" ", MyString))
name = lastname & ", " & firstname
FlipNames = Trim(name)
End If
End Function

 

[austin350s10]

love it thanks a bunch!!!

 

[jproffer]

No problem. Glad to help.

Maybe by now you've noticed this, but it works either way...


Bob Smith - becomes - Smith, Bob

Smith, Bob - becomes - Bob Smith

 

[austin350s10]

Def...I like the toggle feature. Gotta keep that one in my pocket