Switching position of last name & first name strings without affecting position of an existing middle name string

[jnathan]

My data shows employee names in the following convention; lastname middlename firstname e.g. Persie Van Robin. I need to format the order of the name so that it appears as firstname middlename lastname e.g. so it appears as Robin Van Persie. A middlename will in many cases not exist.

My macro successfully swaps the name convention order as required when a middlename does not exist however if it does then it treats the middlename and lastname as one single lastname e.g. the name will be formatted as Van Persie Robin.

Is anyone able to adapt the macro I have to solve the problem described above? Any help will be greatly appreciated!!! The macro is below:

Sub NameSwap()
' this macro swaps positions of lastname and firstname string

Dim i, LastRow

' switches last & first names around
LastRow = Range("B" & Rows.Count).End(xlUp).Row ' this line simply counts the lastrow to know when to stop
For i = 1 To LastRow
lname = Application.Proper(Mid(Cells(i, "B"), _
Application.Find(" ", Cells(i, "B"), 1)))
fname = Application.Proper(Mid(Cells(i, "B"), 1, _
Application.Find(" ", Cells(i, "B"), 1) - 1))
If Cells(i, "B").Value <> "" And _
Application.Find(",", Cells(i, "B"), 1) = 0 Then
Cells(i, "B").Value = lname & "" & fname
End If
Next

End Sub

 

[jnathan]

And what if the middle name is the only part of the name that is potentially spelt with a lower case? i.e. as many Dutch names are - Robin van Persie

Ah, found it. It's in the Proper statements. McDonald is changed to Mcdonald.

If the original names are already properly cased (i.e. Persie instead of PERSIE), you can leave out the Application.Proper statements

 

[JLeno]

Then only use Proper (or if it needs to be lower case, use Lower) on the mName, not on lName and fName

 

[jnathan]

As so..?

mName = Application.Proper(Replace(.Value, lName, "")

Then only use Proper (or if it needs to be lower case, use Lower) on the mName, not on lName and fName

 

[jnathan]

Although the below doesn't quite work - is 'Lower' being placed correctly?

mName = Application.Lower(Replace(.Value, lName, "")

 

[jnathan]

Isn't there a way to code something like; if mName = lname then delete lname?

 

[jnathan]

Done!

If lName = mName Then mName = ""

Isn't there a way to code something like; if mName = lname then delete lname?

 

[JLeno]

Try this:

Sub NameSwap()
' this macro swaps positions of lastname and firstname string
Dim i, LastRow
Dim lName As String, fName As String, mName As String
Dim tempName As String
' switches last & first names around
LastRow = Range("B" & Rows.Count).End(xlUp).Row ' this line simply counts the lastrow to know when to stop
For i = 1 To LastRow
    
    tempName = RTrim(Cells(i, "B").Value)
    
    If tempName <> "" Then
        lName = Left(tempName, InStr(tempName, " ") - 1)
        fName = Right(tempName, Len(tempName) - InStrRev(tempName, " "))
        mName = Replace(tempName, lName, "")
        mName = Trim(Replace(mName, fName, ""))
        If Len(mName) > 0 Then mName = LCase(mName) & " "
        Cells(i, "B").Value = fName & " " & mName & lName
    End If
Next
End Sub

 

[jnathan]

Fantastic - many thanks for all your help and advice. Really helped me get to a place where things are working for me

Try this:

Sub NameSwap()
' this macro swaps positions of lastname and firstname string
Dim i, LastRow
Dim lName As String, fName As String, mName As String
Dim tempName As String
' switches last & first names around
LastRow = Range("B" & Rows.Count).End(xlUp).Row ' this line simply counts the lastrow to know when to stop
For i = 1 To LastRow
    
    tempName = RTrim(Cells(i, "B").Value)
    
    If tempName <> "" Then
        lName = Left(tempName, InStr(tempName, " ") - 1)
        fName = Right(tempName, Len(tempName) - InStrRev(tempName, " "))
        mName = Replace(tempName, lName, "")
        mName = Trim(Replace(mName, fName, ""))
        If Len(mName) > 0 Then mName = LCase(mName) & " "
        Cells(i, "B").Value = fName & " " & mName & lName
    End If
Next
End Sub

 

[JLeno]

You're welcome Good luck with it!