Do you want to learn how to reverse names in Excel with some exciting formulas? This article is for you. Here we discussed 5 simple and handy methods to reverse names in Excel.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
5 Methods to Reverse Names in Excel
Here, we have a list of the Full Name of some employees of a company. Now, we will demonstrate the procedures for reversing the names of the employees according to your needed orders.
Not to mention that we have used Microsoft Excel 365 to create this article, but you can use any other version at your convenience.
1. Using Flash Fill Feature to Reverse Names in Excel
In the beginning, we can use the Excel Flash Fill feature to reverse names in Excel.
To reverse the Full Name, follow the steps below.
📌 Steps:
- First, write the first name in your desired order like in the screenshot shown below.
- Then select the first cell of the Reverse Name column and go to the Home tab >> Fill drop-down >> Flash Fill.
- Next, click on cell C5 and then drag down the Fill Handle tool for other cells.
- After that, if the displayed result is your desired one, then click on the icon shown in the figure and select Accept suggestions.
Therefore, you’ll see that the given names have been reversed. This is how to reverse names in Excel.
2. Applying MID, SEARCH, and LEN Functions to Reverse Names in Excel
In this method, we use the combination of MID, SEARCH, and LEN functions to reverse the names.
📌 Steps:
- Select cell C5 and Write down the function stated below.
You can also write it on the function box.
Here, B5 is the First Name of the employee.
Formula Breakdown:
- LEN(B5) → becomes
- LEN(“Henry Matt”) → The LEN function determines the length of the characters
- Output → 10
- SEARCH(” “,B5) → becomes
- SEARCH(” “,“Henry Matt”) → The SEARCH function finds the position of space in the text Henry Matt
- Output → 6
- SEARCH(” “,B5)+1 → becomes
- 6+1 → 7
- B5&” “&B5 → becomes
- “Henry Matt”&” “&“Henry Matt” → The Ampersand Operator will add up the two texts Henry Matt
- Output → “Henry Matt Henry Matt”
- MID(B5&” “&B5,SEARCH(” “,B5)+1,LEN(B5)) → becomes
- MID(“Henry Matt Henry Matt”,7,10) → Here, 7 is the start number of the characters and 10 is the total number of characters which we will extract using the MID function from the text “Henry Matt Henry Matt”.
- Output → Matt Henry
- MID(“Henry Matt Henry Matt”,7,10) → Here, 7 is the start number of the characters and 10 is the total number of characters which we will extract using the MID function from the text “Henry Matt Henry Matt”.
- “Henry Matt”&” “&“Henry Matt” → The Ampersand Operator will add up the two texts Henry Matt
- SEARCH(” “,“Henry Matt”) → The SEARCH function finds the position of space in the text Henry Matt
- LEN(“Henry Matt”) → The LEN function determines the length of the characters
- After writing the function press ENTER and you will get the result.
- Use FIll Handle for the other cells and this will flip the names.
After that, you will have the following result.
3. Flipping Names with Comma in Excel
Sometimes your dataset has names separated by a comma. If you want to switch it, then follow the steps below.
📌 Steps:
- Select cell C5 and write down the functions stated below.
Here, B5 is the First Name of the employee.
Formula Breakdown:
- LEN(B5)-1 → becomes
- LEN((“Henry, Matt”)-1) → The LEN function determines the length of the characters
- Output → 10
- SEARCH(“, “,B5) → becomes
- SEARCH(“, “,“Henry, Matt”) → The SEARCH function finds the position of space in the text Henry Matt
- Output → 6
- SEARCH(” “,B5)+2 → becomes
- 6+2 → 8
- B5&” “&B5 → becomes
- “Henry, Matt”&” “&“Henry, Matt” → The Ampersand Operator will add up the two texts Henry Matt
- Output → “Henry, Matt Henry, Matt”
- =MID(B5&” “&B5,SEARCH(“,”,B5)+2,LEN(B5)-1)→ becomes
- MID(“Henry, Matt Henry, Matt”,8,10) → Here, 8 is the start number of the characters and 10 is the total number of characters which we will extract using the MID function from the text “Henry, Matt Henry, Matt”.
- Output → Matt Henry
- MID(“Henry, Matt Henry, Matt”,8,10) → Here, 8 is the start number of the characters and 10 is the total number of characters which we will extract using the MID function from the text “Henry, Matt Henry, Matt”.
- “Henry, Matt”&” “&“Henry, Matt” → The Ampersand Operator will add up the two texts Henry Matt
- SEARCH(“, “,“Henry, Matt”) → The SEARCH function finds the position of space in the text Henry Matt
- LEN((“Henry, Matt”)-1) → The LEN function determines the length of the characters
- Next, press ENTER after writing the functions.
- Lastly, use Fill Handle for the other cells and this will switch your names.
-
Afterward, the following results will appear in the Reverse Name column.
Similar Readings
- How to Reverse Text to Columns in Excel (6 Handy Methods)
- How to Reverse X Axis in Excel (4 Quick Tricks)
- Reverse Legend Order of Stacked Bar Chart in Excel (With Quick Steps)
- How to Reverse Order of Columns Vertically in Excel (3 Ways)
- How to Reverse the Order of Worksheets in Excel (3 Easy Ways)
4. Flipping Names in Excel Without a Comma
If your dataset has names without a comma but you want to flip it with a comma then follow the steps.
📌 Steps:
- First, select the cell C5 and write down the functions stated below
Here, B5 is the First Name of the employee.
Formula Breakdown:
- LEN(B5)+1 → becomes
- LEN((“Henry Matt”)+1) → The LEN function determines the length of the characters
- Output → 11
- SEARCH(“, “,B5)+1 → becomes
- SEARCH((“, “, “Henry Matt”)+1) → The SEARCH function finds the position of space in the text Henry Matt
- Output → 6+1→7
- B5&”, “&B5 → becomes
- “Henry Matt”&”,”&“Henry Matt” → The Ampersand Operator will add up the two texts Henry Matt
- Output → “Henry Matt, Henry Matt”
- =MID(B5&” “&B5,SEARCH(“,”,B5)+2,LEN(B5)-1)→ becomes
- MID(“Henry Matt, Henry Matt”,7,11) → Here, 7 is the start number of the characters and 11 is the total number of characters which we will extract using the MID function from the text “Henry Matt, Henry Matt”.
- Output → Matt, Henry
- MID(“Henry Matt, Henry Matt”,7,11) → Here, 7 is the start number of the characters and 11 is the total number of characters which we will extract using the MID function from the text “Henry Matt, Henry Matt”.
- “Henry Matt”&”,”&“Henry Matt” → The Ampersand Operator will add up the two texts Henry Matt
- SEARCH((“, “, “Henry Matt”)+1) → The SEARCH function finds the position of space in the text Henry Matt
- LEN((“Henry Matt”)+1) → The LEN function determines the length of the characters
- Press ENTER.
- Use Fill Handle for the other cells and reverse the names without commas.
-
Finally, you will have the following result.
5. Reversing Names Using Excel VBA
Lastly, we can also reverse the name using the VBA code, a programming language for Microsoft Excel and other office tools.
📌 Steps:
- Go to the Developer tab >> Visual Basic option.
- Click on the Insert tab and then select the Module
Afterward, Module 1 will be created where we will insert our code.
- Write down the following VBA code inside the created module
Sub name_flip()
Dim rng As Range
Dim wrk_rng As Range
Dim sym As String
On Error Resume Next
Set wrk_rng = Application.Selection
Set wrk_rng = Application.InputBox("Range", "Exceldemy", wrk_rng.Address, Type:=8)
sym = Application.InputBox("Symbol interval", "Exceldemy", " ", Type:=2)
For Each rng In wrk_rng
yValue = rng.Value
name_list = VBA.Split(yValue, sym)
If UBound(name_list) = 1 Then
rng.Offset(0, 1) = name_list(1) + sym + name_list(0)
End If
Next
End Sub
Here, name_flip is the sub-procedure name. We have declared rng, wrk_rng as Range, sym as String.
- Next, run the code by pressing the F5 button and an input box will appear.
- Select all the cells you want to reverse (here, $B$5:$B$8 is our selected range) and press OK.
- Then, another input box will pop up.
- Type a comma (,) as the symbol for interval and press OK.
- Consequently, you will get your result.
Read More: How to Reverse a String in Excel (3 Suitable Ways)
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Conclusion
So, these are some easy methods to Reverse Names in Excel. Please let us know in the comment section if you have any questions or suggestions. For your better understanding please download the practice sheet. Visit our website Exceldemy to find out different kinds of excel methods. Thanks for your patience in reading this article.
I Want to Copy many Rows by Selection from one sheet to another sheet using Macro. Is it possible?
Hi, Muhammad! Of course, you can do that. Check this out 🙂
https://www.exceldemy.com/excel-macro-to-copy-and-paste-from-one-worksheet-to-another/#9_VBA_Macro_to_Copy_and_Paste_Selected_Data_from_One_Sheet_to_Another_in_Excel