In excel how do you reverse the name from ex. linda davis to davis linda without having to do too much cutting?

2 Comments

1. Andrew L says:

   at

   If all your data is in a single column A, put this formula in B1:-

   =RIGHT(A1,LEN(A1)-FIND(" ",A1))&" "&LEFT(A1,FIND(" ",A1)-1)

   Drag down as required.

   NOTE: This only works with a single first name and a single last name! But at least you will get most of the work done in one operation.
2. garbo7441 says:

   at

   Here is a macro the will query for the column letter containing the names, then reverse them as you wish. It will not handle ‘middle names’ at this point, i.e. ‘Linda Lee Davis’. If you have any, you may have to manually correct them.

   Copy this macro to the clipboard:

   Sub Reverse_Names()
   Dim i, LastRow
   selcol = InputBox("Please enter the column letter with names", "Select Column")
   LastRow = Range(selcol & Rows.Count).End(xlUp).Row
   For i = 1 To LastRow
   pos = Val(Application.Find(" ", Cells(i, selcol)))
   Cells(i, selcol).Value = Application.Proper(Mid(Cells(i, selcol), pos + 1, _
   (Len(Cells(i, selcol)) – (pos – 1))) & " " & Left(Cells(i, selcol), pos – 1))
   Next
   End Sub

   Next, press ALT + F11

   In the menus at the top of the VBE, select INSERT MODULE.

   Paste the macro into the module editing area to the right.

   Close the VBE, which will return you to Excel.

   Press ALT + F8

   When the Macros window opens, click on this macro and then click ‘Options’.

   Enter a letter to be used as a keyboard shortcut and click ‘OK’.

   Close back to Excel and press CTRL + your letter to run the macro. You will be prompted to enter your column letter, then all names will be restructured.

   EDIT: if you do have middle names, then this macro should work:

   Sub Reverse_Middle_Names()
   Dim i, LastRow
   selcol = InputBox("Please enter the column letter with names", "Select Column")
   LastRow = Range(selcol & Rows.Count).End(xlUp).Row
   For i = 1 To LastRow
   numWords = Len(Cells(i, selcol)) – Len(Application.Substitute(Cells(i, selcol), " ", ""))
   If numWords = 1 Then
   pos = Val(Application.Find(" ", Cells(i, selcol)))
   Cells(i, selcol).Value = Application.Proper(Mid(Cells(i, selcol), pos + 1, _
   (Len(Cells(i, selcol)) – (pos – 1))) & " " & Left(Cells(i, selcol), pos – 1))
   ElseIf numWords = 2 Then
   pos = Val(Application.Find(" ", Cells(i, selcol)))
   Cells(i, selcol).Value = Application.Proper(Mid(Cells(i, selcol), pos + 1, _
   (Len(Cells(i, selcol)) – (pos – 1))) & " " & Left(Cells(i, selcol), pos – 1))
   pos = Val(Application.Find(" ", Cells(i, selcol)))
   Cells(i, selcol).Value = Application.Proper(Mid(Cells(i, selcol), pos + 1, _
   (Len(Cells(i, selcol)) – (pos – 1))) & " " & Left(Cells(i, selcol), pos – 1))
   End If
   Next
   End Sub