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