How to flip the first and last name in cells in Excel?
Let's say you get a roster of names in one column, however, you need to flip the first name and last name in cells. Normally you will get into one cell and cut the last name, and then paste it before the first name, or retype the name again. It will be time-consuming and tedious if you use these traditional methods. Actually, there are several easy ways to flip or reverse the first name and last name in a single column quickly. Supposing you have a column of names, and you need to flip the first and last name as below screenshot shown:
Flip the first and last name in a column with a function
Flip the first and last name in a column with VBA
Flip the first and last name in a column with Kutools for Excel
Split full name into first name and last name with Kutools for Excel
Flip the first and last name in a column with a function
We can use one of Excel's formulas to flip the first and last name quickly in a column with following steps:
Enter the formula =MID(A2&" "&A2,FIND(" ",A2)+1,LEN(A2)) in a blank cell, says Cell C2. And then drag the fill handle down to the cells to apply this formula, then you can see all first and last names have been revered. See screenshot:
Flip the first and last name in a column with VBA
You can also use VBA macros to flip the first and last name in a column quickly.
1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module window.
Sub FlipName()
'Updateby20131126
Dim Rng As Range
Dim WorkRng As Range
Dim Sign As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
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 Sub3. Press the F5 key to run this macro. In the popping up KutoolsforExcel dialog box, select the range to flip, and then click the OK button. See screenshot:
4. In the following popping up dialog box, please enter an interval symbol (such as a space), and then click the OK button. See screenshot:
5. Now the first name and last name in selected range are flipped. See screenshot:
Flip the first and last name in a column with Kutools for Excel
With Kutools for Excel’s Reverse Text Order, you can quickly reverse order of the text in the cells.
- 300+ powerful and easy-to-used functions,110,000+ Excel users'choice
- full-featured free trial in 30 days, no credit card required
- dozens of one-click functions to shrink your working time on solving complex problems
- includes functions to batch converion,deletion,combining sheets/cells,exproting, ect, free try now.
After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
1. Select the range that you want to reverse the first and last names.
2. Click Kutools > Text > Reverse Text Order, see screenshot:
3. In the Reverse Text dialog box, specify a separator your selected text string using, (in this case, I select Space option) and then click the OK button. And all the first names and last names in the selected range have been reversed. See screenshot:
Note: Checking Skip non-text cells to prevent you reversing the numbers in selected range.
Demo: Flip / Reverse the first and last names in cells
Split full name into first name and last name with Kutools for Excel
After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
If you have a list of full names which need to split to first name and last name in two columns separately as below screenshot shown, you can apply Kutools for Excel’s Split Names to quickly handle it.
1. Select the list of names you want to split, click Kutools > Merge & Split > Split Names.
2. In the Split Names dialog, check First name and Last name checkboxes, if you want to split middle name as well, check Middle name. Click Ok, select a cell to place the split names.
Demo: Split Names into First name and Last Name
| Kutools for Excel: 300+ functions you must have in Excel, 30-day free trial from here. |
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
