Usually, the names are either in the ‘First Name Last Name’ format (such as Joe Davis or Sumit Bansal). In some specific situations, you may want the names data to be formatted as Last Name, First Name (i.e., last name followed by a comma and then the first name) In this tutorial, I will show you how to switch the first and the last name in Excel with a comma in between (when you have your data in the First Name Last Name format)
Switch First and Last Name Using Flash Fill
One of the easiest and fastest ways to switch the first and the last name with a comma in between is by using the Flash Fill feature in Excel. Flash Fill is an amazing tool that works by identifying patterns in your data set (where you need to enter one or two expected result entries for it to identify the pattern). Let me show you how it works. Below I have a data set where I have the names in column A, and I want to change this data set by switching the first and the last name and adding a comma in between the names. Here are the steps to do this using Flash Fill: That’s it! As soon as you click the Flash Fill option, you should see the entire column automatically get filled with the correct result. Let me quickly explain how this works. When I entered the expected result in cell B2 and then used Flash Fill, it used the value in cell B2 to identify the pattern and replicated it for all the cells in the column. So it was able to correctly identify that I wanted to change the names data set by switching the first and the last name and adding a comma in between. Pro Tip: You can use the keyboard shortcut Control + E to run the Flash Fill. So as instead of going to the Home tab and then clicking on the Flash Fill icon, enter the expected result in one cell, hit the Enter key, and then hold the Control key and press the E key. Caution: Since Flash Fill works by identifying the pattern in your data set, it is important that you have a consistent data set. Also, cross-check the result from Flash Fill to ensure that it has picked up the correct pattern. Note – The result that you get from Flash Fill is static. So if your original data changes, the resulting data will not automatically update, and you will have to repeat the process.
Switch First and Last Name Using Formula
Let me now show you how you can switch the first and the last name by using a simple formula. While there is no inbuilt function to do this, it can quickly be done by using a combination of text functions. Below I have the names data set where I want to switch the first and the last name and add a comma in between. Here is the formula that will do this: Enter this formula in cell B2 and copy it for all the other cells in the column. Let me explain how this formula works. In the formula, I have used the LEFT and the RIGHT function to extract the first name and the last name, respectively. This is made possible by identifying the position of the space character (using the FIND function). Using the position of the space character, I extracted all the characters on the left, which would be the first name, using the LEFT function. And similarly, I extracted all the characters to the right of the space character using the RIGHT function (which gave me the last name). And then, I combined the last name and the first name by using the CONCAT function. Also, since I wanted the last name and the first name to have a comma in between, I added a comma followed by a space character as the second argument in the CONCAT function. You can also use the below formula, which uses the & operator instead of the CONCAT function: Beware of the Extra Spaces: For this formula to work correctly, you have to make sure that there is only one space character between the first name and the last name. In case there is a possibility that there could be multiple spaces in between the names or there could be a leading or trailing space, then it would be best to use TRIM(A2) instead of A2 in the above formulas. Also, note that since we are using a formula to switch the names, in case you change the original data set, the resulting data will automatically update to give you the correct result. And if you do not need the original data anymore, do not just delete the original data without first converting the formulas into values. To do this, select the cells that have the result, copy the cells right-click and then paste them as values.
Switch First and Last Name Using Text to Columns with CONCAT Function
You can also use the Text to Columns feature to split the first name and last name into separate columns and then combine them in the order you want with the delimiter you want. Let me show you how this works. Below I have the names data set where I want to switch the position of the first name and the last name and add a comma in between. Here are the steps to split the first name and the last name into separate columns using the Text to Columns feature: The above steps would split the names data set into two separate columns – one with first names and the other with last names. Once you have the first and the last name in separate columns, you can use the formula below to combine these names in the desired order (i.e., the last name followed by a comma and then the first name) Enter the formula in cell D2 and copy it for all the cells in the column to get all the names. The above formula uses the CONCAT function to combine three elements:
The last name, which is in cell B2 A comma followed by a space character (which needs to be in double quotes) The first name, which is in cell B2
Switch First and Last Name Using Power Query
Power Query is another way to switch the first and the last name and add the desired delimiter in between. The Power Query method should only be used if you are already using It to transform your data and, as a part of it, also wants to change the names data set. Or if you have to do this quite often so, instead of doing it using the Flash Fill method or the formula method every time, you can use Power Query to do it once and then use the same query over and over again with different data sets. But if this is a one-time activity where you want to reverse the position of the first name and the last name, it is best to use the other methods such as Flash Fill or formula (covered earlier in this tutorial) Below I have the names data set where I want to switch the first and the last name and separate them by a comma and a space character. Below are the steps to do this using Power Query: The above steps would insert a new worksheet in your workbook and give you the resulting data in an Excel Table. I’m sure you’re thinking about how long this method is and whether this is worth it or not. As I mentioned at the beginning of this section, if you only want to do this once or twice, it’s best not to use Power Query. But if this is something you need to do quite often, then you can follow all the above steps to create the query once, and the next time you have a new data set, you can connect your query to that table and get the result instantly. Or if you make any changes to the already existing names data set, you don’t need to repeat the process. All you need to do is right-click on the resulting Excel Table that we got and then click on refresh, and it will perform all the steps in the back end for you and give you the result.
Switch First and Last Name Using VBA
Now let me show you how to reverse the first and the last name using a simple VBA code. Below is the VBA macro code that would switch the first and the last name position and add a comma in between The above VBA code uses a For Each Next loop to go through each cell in the column and then uses the INSTR function to find out the position of the space character in the cell. Once it knows the position of the space character, it uses the MID function and the LEFT function to extract the last name and the first name, respectively, and combine them using an & operator. Here are the steps to use this code: The above steps would instantly change your original data set and give you the result as shown below: While there is some initial setup required to use the VBA method, it is helpful in case you want to change the names data set in multiple locations in your existing workbook. You can also save this macro code in the Personal Macro Workbook so that it can be used in any workbook on your system. This would be quite a time saver as you can open any Excel workbook where you have the names data and then run this code to switch the first and the last name (separated by a comma). Click here to learn about Personal Macro Workbook and how you can save your code there to reuse on any workbook in your system Pro Tip: if you save the macro in your Personal Macro Workbook, you can save more time by adding the macro icon to the Quick Access Toolbar. Once added, you can run the code with a single click by clicking on the macro icon in the QAT So these are the five methods you can use to reverse the first name and the last name and add a comma in between the names. If you need to do it once in a while, you can use the Flash Fill method, the formula method, or the Text to Columns method. And if you need to do this quite often, consider using the Power Query or the VBA method. Other Excel articles you may also like:
How to Shuffle a List of Items/Names in Excel? 2 Easy Formulas! Extract Last Name in Excel (5 Easy Ways) How to Combine First and Last Name in Excel (4 Easy Ways) How to Sort by the Last Name in Excel (Easy Guide) Separate First and Last Name in Excel (Split Names Using Formulas) Separate Text and Numbers in Excel
