How to Reverse First Name and Last Name

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.

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

reverse first name and last name1

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

reverse first name and last name2

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))

reverse first name and last name3

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

reverse first name and last name7

Sub ReverseName()
    Dim Rng As Range
    Dim WorkRng As Range
    Dim Sign As String
    On Error Resume Next
    xTitleId = "ReverseName-ExcelHow"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Sign = Application.InputBox("Symbol interval", xTitleId, " ", Type:=2)
    For Each Rng In WorkRng
        xValue = Rng.Value
        NameList = VBA.Split(xValue, Sign)
        If UBound(NameList) = 1 Then
            Rng.Value = NameList(1) + Sign + NameList(0)
        End If
    Next
End Sub

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

reverse first name and last name8

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

reverse first name and last name4

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

reverse first name and last name5

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

reverse first name and last name6


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)…

 

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar