Choosing a name format when working on name data sets in Excel is essential, and it depends on the project or its requirements. However, you will find this article useful if you want to switch first and last names with a comma.
It is applicable in a case where you want to have the last name first before the first name. Let's see how you can do this.
Option 1: Use the Fill Feature
The Fill feature is a great choice to switch first and last names using a comma in Excel. The Fill feature will identify the existing pattern in your data set and replicate the same for the empty cells you intend to fill.
For instance, if you have a names data set as shown in the image below:
You can switch the first and last names with a comma by manually typing the expected result for the first name before applying the feature to the remaining items.
That is, once you type the expected result in the first cell, the Fill feature will identify the pattern and the arrangements you have and apply the same to all other cells as required. Here are the steps to use this feature:
- Open the Excel document containing the names data set.
- Enter your expected result for the first cell in the cell next to it, cell B2 in this case, and press the Enter key.
- The app will highlight the next cell which is B3 in this example.
- Click the Home tab in the top ribbon.
- Click the Fill option in the Editing section of the toolbar.
- Next, select the Flash Fill option from the dropdown options. You can also press Ctrl + E after filling the first cell to use the Flash Fill option.
- Scan the result to ensure it is consistent.
The other cells will be filled automatically after clicking the Flash Fill option. However, the auto-fills will follow the same format as your first entry, including capitalization. If you begin the first or last name with a lowercase letter, it will replicate it for all other names, and so on.
Also, ensure the entries on the initial column are consistent to avoid alterations to your data set. For instance, the feature will delete the third name in any cell containing three names if your first entry consists of only two names.
Please note that this feature will not update your results if you change any data after using it. You will need to repeat the process to keep it up-to-date.
Option 2: Use Excel Formula
Excel may not have an inbuilt function for switching first and last names with commas, but you can use a combination of text functions instead. Here is how:
- Open the Excel document with the name data set.
- Click the cell next to the first name where you want the output to display.
- Paste the formula below in the cell.
=CONCAT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),", ",LEFT(A1,FIND(" ",A1)-1))
- Press Enter.
- Repeat the same for the other cells, but you should change the cell names from A1 to the new cell containing the name.
It is advisable to copy and paste the formula to avoid adding an extra character as that will make it invalid. You can only edit the cell names when dealing with data in other cells.
Option 3: Use Power Query
You can use Power Query if you need to perform name switch in Excel more often, but the method may not be the best if you need it just once. It is not recommendable for one-time use because it takes a longer process than the Flash Fill option. However, it is your best bet if you will perform similar operations often. All you need to do in that case is to create a query once and link it to every table you need to work on.
Here are the steps to use power query to switch first and last names in Excel with a comma:
- Open the Excel document containing the names data.
- Create a header for the column containing the names.
- Select all the cells containing the names
- Click the Data tab at the top ribbon.
- Next, Select the From Table/Range option in the Get & Transform Data section in the toolbar.
- A create Table dialog box will appear, ensure that the “my table has headers column is checked,” then click OK.
- Right-click on the Names column from the Power Query Editor window and hover the mouse over the Split Column option.
- Next, select By Delimeter and another dialog box will display.
- Select Space as the delimiter and click OK to continue. This action will split the name into first name and last name in the Power Query Editor.
- Select both name columns in the Editor, Right-click on the column headers, and select Merge Columns from the context menu. You can select both columns by clicking the header of the first column, then holding the Ctrl key before clicking the header of the second column.
- Click the Separator drop-down in the Merge Columns box and choose the Custom option.
- Next, input the comma character followed by a space (, ) in the new field that appears.
- Enter a new title for the Merged column, say, Full names, and click OK.
- Click the Close and Load option at the top ribbon to complete.
Conclusion
You can switch first and last names in Excel with a comma using any of the methods above, but the Power Query option is best if you will perform the action repeatedly. Otherwise, the Flash Fill and formula methods will suit you better.
You may also like to read:
How To Insert Signature in Word
How to Insert a Signature in Excel
How to Download Music from Tidal on Computer
How To Remove Empty Cells In Excel
How To Change Date Format In Excel To dd/mm/yyyy