In this post, you will get to know how to reverse names in Excel with some superb formulas. You will find ways to reverse names in Excel such as the Flash Fill feature, the use of MID, SEARCH, and LEN functions, the use of commas, and VBA macro.
Without further ado, let’s dive in to see how you can perform this function.
How to Reverse Names in Excel with Flash Fill Feature?
Our first method starts with the Flash Fill feature to reverse names in Excel. Follow the steps given below:
The following steps will help in reversing the full name:
- Add the first name the way you like.
- Choose the first cell of the Reverse Name column.
- Open the Home tab and choose the Fill drop-down menu.
- From the drop-down menu click on the Flash Fill option.
- Click on cell C5 and use the Fill Handle tool to apply the same procedure on all other cells.
- The result will be in front of you. click on the icon that appears on the screen and choose to Accept Suggestions.
That’s it!
Your given names are now reversed in Excel with a simple method.
How to Reverse Names in Excel with MID, SEARCH, and LEN Functions?
In this method, you will learn how MID, SEARCH, and LEN functions help in reversing the names.
- Choose cell C5 and enter the formula given below:
=MID(B5&””&B5,SEARCH(“ “,B5)+1LEN(B5))
- This formula is also helpful when you type it in the function box.
- B5 here is the First Name of the employee.
- Once the function is added, press ENTER key and the results will be in front of you.
- Now, the Fill Handle function is used to apply the procedure to all other cells.
Below you can see the results.
How to Reverse Names in Excel with Commas?
The following steps will guide you on how you can flip names in Excel when your dataset contains commas to separate names.
- Choose cell C5 and enter the following formula:
=MID(B5&” “&B5,SEARCH(“,”,B5)+2,LEN(B5)-1)
- B5 here is the First Name of the employee.
- Now, press ENTER once the above function is added.
- Use the Fill Handle feature to apply the entire procedure on all other cells to flip names.
You will find the results in the Reverse Name column. That’s it!
How to Reverse Names with Excel VBA?
VBA is not the end solution but it is an effective way to sort out any kind of problem you face while working on Excel. You can use the VBA code to reverse the names.
- Open the Developer tab and choose the Visual Basic option.
- Select the Insert tab and choose the Module option from the menu.
- Now, Module 1 is generated in which you will have to put the code.
Below is the VBA code to enter the 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.
- Press F5 to run the code and you will see an input box.
- Choose all the cells you need to reverse and then press OK.
- In this example, $B$5:$B$8 is the selected range of which you need to reverse names.
After that, you will see another input box.
Here, you have to add a comma as the sign for the interval.
- Press OK.
Final Thoughts:
This post clearly explains how to reverse names in Excel by using some easy methods. You can try all of the above methods without any hesitation. Each method is super valuable and you will get the desired results.
