Flip or reverse first and last names in Excel list
If you have a list of full names which are formatted as last name and first name, now, you want to flip the last and first names to first and last as below screenshot shown. This article, I will introduce some formulas for dealing with this job in Excel.
- Flip or reverse the first and last names (Stone Jerry = Jerry Stone)
- Flip or reverse the first and last name with comma (Stone, Jerry = Jerry Stone)
- Flip or reverse the first and last name without comma (Stone Jerry = Jerry, Stone)
- Flip or reverse the first and last name based on any separators with a useful feature
Flip or reverse the first and last names (Stone Jerry = Jerry Stone)
Normally, in the Excel, the MID function which combines the SEARCH and LEN functions can help you to flip the first and last names as you need.
If your full name is last name first name format, to reverse the first and last names, please apply the following formula into a blank cell where you want to get the result:
Then, drag the fill handle down to the cells that you want to use this formula, and all the first and last names have been reversed as below screenshot shown:
Explanation of the formula:
A2&" "&A2: This part is used to join the cell content in A2 and A2, and separate the merged data by a space, you will get the result: “Stone Jerry Stone Jerry”, this is recognized as the text argument in MID function.
SEARCH(" ",A2)+1: The SEARCH function is used to find the position of the space character in cell A2, adding 1 means to get the position of the first character after the space. This part is recognized as the start_num argument in the MID function.
LEN(A2): This LEN function returns the length of the text in cell A2, it is recognized as the num_chars argument in the MID function.
MID(A2&" "&A2,SEARCH(" ",A2)+1,LEN(A2)): So far, the MID function is used to extract the text from “Stone Jerry Stone Jerry” which start at the position returned by the SEARCH function and the length returned by the LEN function.
Flip or reverse the first and last name with comma (Stone, Jerry = Jerry Stone)
Supposing, your last and first names are separated by comma, now you want to reverse the last and first names and skip the comma as below screenshot shown, how could you solve this task in Excel?
Please copy or enter the below formula into a blank cell:
And then, drag the fill handle down to the cells that you want to apply this formula, and you will get the result as you need, see screenshot:
Explanation of the formula:
A2&" "&A2: This part is used to join the cell content in A2 and A2, and separate the merged data by a space, you will get the result: “Stone, Jerry Stone, Jerry”, this is recognized as the text argument in MID function.
SEARCH(",",A2)+2: The SEARCH function is used to find the position of the comma character in cell A2, adding 2 means to get the position of the first character after the comma. This part is recognized as the start_num argument in the MID function.
LEN(A2)-1: This LEN function returns the length of the text in cell A2, subtracting 1 means to exclude the comma, this is recognized as the num_chars argument in the MID function.
MID(A2&" "&A2,SEARCH(",",A2)+2,LEN(A2)-1): So far, the MID function is used to extract the text from “Stone, Jerry Stone, Jerry” which start at the position returned by the SEARCH function and the length returned by the LEN function
Flip or reverse the first and last name without comma (Stone Jerry = Jerry, Stone)
To flip the first and last names which are separated by space, after flipping the names, you want to add comma after the first name as following screenshot shown. To solve this job, here is another formula can help you.
Please copy or enter the below formula into a blank cell:
Then, drag the fill handle down to the cells to copy this formula to other cells you need, and the names have been reversed, a comma has been inserted after the first name as well, see screenshot:
Explanation of the formula:
A2&", "&A2: This part is used to join the cell content in A2 and A2, and separate the merged data by a comma and a space, you will get the result: “Stone Jerry, Stone Jerry”, this is recognized as the text argument in MID function.
SEARCH(" ",A2)+1: The SEARCH function is used to find the position of the space character in cell A2, adding 1 means to get the position of the first character after the space. This part is recognized as the start_num argument in the MID function.
LEN(A2)+1: This LEN function returns the length of the text in cell A2, adding 1 means to include the comma, this is recognized as the num_chars argument in the MID function.
MID(A2&", "&A2,SEARCH(" ",A2)+1,LEN(A2)+1): So far, the MID function is used to extract the text from “Stone Jerry, Stone Jerry” which start at the position returned by the SEARCH function and the length returned by the LEN function.
Tips:
1. If your full names contain first name, middle name and last name, to reverse these names, you should apply the below formula:
2. To add a comma between the reversed last and first names, please use the following formula:
Flip or reverse the first and last name based on any separators with a useful feature
Kutools for Excel supports a handy tool - Reverse Text feature which can help you to reverse the order of the text string within a cell based on space, comma or any other separators as you need. Click to download Kutools for Excel for free trial!
Relative functions used:
- MID:
- The MID function is used to find and return a specific number of characters from the middle of given text string.
- SEARCH:
- In Excel, the SEARCH function can help you to find the position of a specific character or substring from the given text string.
- LEN:
- The LEN function returns the number of characters in a text string.
More articles:
- Extract first initial and last name from full name
- Supposing, you have a list of full names, now, you would like to extract the first initial and last name or first name and first character of last name from the full name as below screenshots shown. How could you deal with these tasks in Excel workbook?
- Extract Multiple Lines From A Cell
- If you have a list of text strings which are separated by line breaks (that occurs by pressing Alt + Enter keys when entering the text), and now, you want to extract these lines of text into multiple cells as below screenshot shown. How could you solve it with a formula in Excel?
- Extract Nth Word From Text String In Excel
- If you have a list of text strings or sentences, now, you want to extract the specific nth word from the list as below screenshot shown. This article, I will introduce some methods for solving this job in Excel.
- Extract Text Between Parentheses From Text String
- If there is part of the text surrounded with the parentheses within the text string, now, you need to extract all the text strings between the parentheses as following screenshot shown. How could you solve this task in Excel quickly and easily?
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
