This post explains that how to flip or reverse the first name and last name in one column. How to reverse the first name and last name in each cell in one column in excel. How to flip the first name and last name with excel formula or excel VBA macro.

If you need to reverse first name and last name in a column and then put the result into another cell in another column.  You need to get or extract the first name or last name firstly and then combine those names into the same cell. You can write an excel formula or write a new excel VBA code to achieve it.

1. Reverse First Name and Last Name with excel formula

To reverse first name and last name, you can create a formula based on the MID function, the FIND function and the Len function as follows:

=MID(B2&" " &B2, FIND(" ",B2)+1, LEN(B2))

Let’s see how this formula works:

=B2&” ” &B2

This formula will use the concatenate operator to join text string in B2 with text string in B2 again to generate a new text string. Then we just need to extract the second and third words from the new text string.

= FIND(” “,B2)+1

This formula will return the position of the first space character in text string of Cell B2.  Then it will pass into the MID function as its start_num argument.

 = LEN(B2)

It will return the length of the text string in Cell B2, then it will pass into the MID function as its num_chars argument.

=MID(B2&” ” &B2, FIND(” “,B2)+1, LEN(B2))

2. Reverse First Name and Last Name with excel VBA macro

You can also write an excel macro to reverse the first name and last name, you can refer to the following steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

4# paste the below VBA code into the code window. Then clicking “Save” button.

Sub ReverseName()
    Set myRange = Application.Selection
    Set myRange = Application.InputBox("Select one Range that you want to reverse name", "ReverseName", myRange.Address, Type:=8)
    myDelemiter = Application.InputBox("Please type a delimiter character:", "ReverseName", " ", Type:=2)
    For Each myCell In myRange
        xValue = myCell.Value
        NameList = VBA.Split(xValue, myDelemiter)
        If UBound(NameList) = 1 Then
            myCell.Value = NameList(1) + myDelemiter + NameList(0)
        End If
    Next
End Sub

5# back to the current worksheet, then run the above excel macro.

6# select the range of cells that contains the first name and last name, then click “OK” button.

7# enter one delemiter character, such as: sapce character in the text box. Then click OK button.

8# you will see that the first name and last name have been reversed in the selected range of cells.

3. Video: Reverse First Name and Last Name

Welcome to an Excel video tutorial, we’re unveiling a powerful skill—how to reverse first names and last names in Excel. Join us as we explore two dynamic methods—one harnessing the power of Excel formulas and the other leveraging VBA code.

https://youtu.be/lTdrjEq1zJ0

4. Related Formulas

Get Last Name From Full Name
To extract the last name within a Full name in excel. You need to use a combination of the RIGHT function, the LEN function, the FIND function and the SUBSTITUTE function to create a complex formula in excel..…

• Get First Name From Full Name
  If you want to get the first name from a full name list in Column B, you can use the FIND function within LEFT function in excel. The generic formula is as follows:=LEFT(B1,FIND(” “,B1)-1).…
• Split full name to first and last name
  You can create another formula based on the RIGHT function, the LEN function, the FIND function and the SUBSTITUTE function to extract the last name……
• Extract text before first comma or space
  If you want to extract text before the first comma or space character in cell B1, you can use a combination of the LEFT function and FIND function….
• Extract nth word from text string
  If you want to extract the nth word from a text string in a single cell, you can create an excel formula based on the TRIM function, the MID function, the SUBSTITUTE function, the REPT function and the LEN function..…

Related Functions

• Excel MID function
  The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
• Excel FIND function
  The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
• Excel LEN function
  The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…