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

 

[JLeno]

I don't understand in your code why you're looking for ",". There aren't any commas in the original string, right?

This piece of code should do the trick:

Sub NameSwap()
' this macro swaps positions of lastname and firstname string
Dim i, LastRow
Dim lName As String, fName As String, mName 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
    With Cells(i, "B")
        If .Value <> "" Then
            lName = Application.Proper(Left(.Value, InStr(.Value, " ") - 1))
            fName = Application.Proper(Right(.Value, Len(.Value) - InStrRev(.Value, " ")))
            mName = Replace(.Value, lName, "")
            mName = Trim(Replace(mName, fName, ""))
            If Len(mName) > 0 Then mName = mName & " "
            Cells(i, "B").Value = fName & " " & mName & lName
        End If
    End With
Next
End Sub

 

[JLeno]

PS. You can capture the entire thing in a normal function as well. If, Proper, Left, Right, Len, and Trim are normal Excel functions. Instr is called Search, and Replace is called Substitute.

You can probably figure it out then, if you'd want a solution without VBA

 

[jnathan]

Hi JLeno

Thanks for this - it works however the names I am switching have an extra blank character at the end of them, which is affecting the macro's ability to 'read' where the last name ends. Is there an extra line I can insert to ensure that the end of the string within each cell has no additional space characters?

Many thanks for your help - really do appreciate it.

Justin

I don't understand in your code why you're looking for ",". There aren't any commas in the original string, right?

This piece of code should do the trick:

Sub NameSwap()
' this macro swaps positions of lastname and firstname string
Dim i, LastRow
Dim lName As String, fName As String, mName 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
    With Cells(i, "B")
        If .Value <> "" Then
            lName = Application.Proper(Left(.Value, InStr(.Value, " ") - 1))
            fName = Application.Proper(Right(.Value, Len(.Value) - InStrRev(.Value, " ")))
            mName = Replace(.Value, lName, "")
            mName = Trim(Replace(mName, fName, ""))
            If Len(mName) > 0 Then mName = mName & " "
            Cells(i, "B").Value = fName & " " & mName & lName
        End If
    End With
Next
End Sub

 

[JLeno]

Do the original values have trailing spaces at the end, or the newly created ones?

You can delete them using RTrim in VBA, so if Cells(i,"B").Value is "Persie van Robin ", RTrim(.Value) will give you "Persie van Robin".

Does that help you?

 

[jnathan]

All the original values have a trailing space at the end. Sorry for being a bit slow but how will the RTrim within the code? I tried inserting it as 'RTrim (.Value)' under 'With Cells(i, "B")' however it did not remove the trailing space.

Do the original values have trailing spaces at the end, or the newly created ones?

You can delete them using RTrim in VBA, so if Cells(i,"B").Value is "Persie van Robin ", RTrim(.Value) will give you "Persie van Robin".

Does that help you?

 

[JLeno]

Does it work if it's like 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 = Application.Proper(Left(tempName, InStr(tempName, " ") - 1))
        fName = Application.Proper(Right(tempName, Len(tempName) - InStrRev(tempName, " ")))
        mName = Replace(tempName, lName, "")
        mName = Trim(Replace(mName, fName, ""))
        If Len(mName) > 0 Then mName = mName & " "
        Cells(i, "B").Value = fName & " " & mName & lName
    End If
Next
End Sub

 

[jnathan]

It does - only issue is that with names beginning with 'Mc' e.g. McDonald it repeats it as the middle name and last name so Jim McDonald will appear as Jim McDonald McDonald.

Any idea why? Promise to leave you alone after this

Much appreciated

J

Does it work if it's like 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 = Application.Proper(Left(tempName, InStr(tempName, " ") - 1))
        fName = Application.Proper(Right(tempName, Len(tempName) - InStrRev(tempName, " ")))
        mName = Replace(tempName, lName, "")
        mName = Trim(Replace(mName, fName, ""))
        If Len(mName) > 0 Then mName = mName & " "
        Cells(i, "B").Value = fName & " " & mName & lName
    End If
Next
End Sub

 

[JLeno]

It doesn't for me. What is the exact original text? is It "McDonald Jim"?

 

[JLeno]

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