How to Switch First and Last Name in Excel with Comma

Get FREE Advanced Excel Exercises with Solutions!

In this Excel tutorial, you will learn how to switch first and last name in Excel with comma. For example, you have a name like this: Emily Smith, and you want to convert this to: Smith, Emily. In the following image, you see, that we have switched the first and last name in Excel with comma.

how to switch first and last name in excel with comma

Switching first name and last name in Excel using Flash Fill feature.

Switching First and Last name in Excel with comma can be done by several ways: using Excel Flash Fill feature (the easiest method); combining different Excel functions; using Convert Text to Columns Wizard; using Power Query; using Power Pivot; and applying Excel VBA code.

Generally, we need to switch first and last name with comma for documentation or official purposes. If you have to switch first and last name with comma for a large dataset, using Excel would be really handy.

We will use the following dataset to show how to switch first and last name in Excel with a comma. Here, column B contains the first and last names of 5 people. Our goal is to switch the first name and last name with a comma. For example, Emily Smith will be Smith, Emily.

Dataset to switch first and last name in Excel with comma


1.Using Flash Fill feature to Switch First and Last Name in Excel with Comma

Excel’s Flash Fill feature is the easiest way to reverse the first name and last name in Excel with comma.

Flash Fill feature automatically fills in values according to a specific pattern you provide. You have to provide it with an example to recognize the pattern but sometimes you need to provide it with a couple of examples (when dataset is complex) to recognize the pattern that you want as output.

We shall use the Flash Fill feature in two ways to switch first and last name in Excel with comma. The first way is using Flash Fill from the Home tab, and another way is using it from the Fill Handle tool. Let’s start with using the Flash Fill option from the Home tab to switch first and last name with comma.


1.1 Using Flash Fill Option from the Home Tab

In this part, we shall use the Flash Fill option which is under the Editing group of commands of the Home tab. We will use the previously mentioned dataset. Follow the steps:

Step 1: Select cell C5 and input the name like this: Smith, Emily.

Using Flash Fill to switch first and last name with comma

Note: Be careful about the consistency of the pattern in your data. For example, you must have only the First Name and Last Name in cells. If all the cells don’t have a similar pattern, then Flash Fill won’t identify the pattern correctly. That will result in giving wrong outputs. So, it’s a good practice to cross-check the data.

Step 2: Go to the Home tab => Editing group of commands => Click on the Fill dropdown =>You will get the Flash Fill command.

Selecting Flash Fill from Home tab

Step 3: Click on the Flash Fill option and you will get the First Name Last Name switched to format Last Name, First Name.

Output of using Flash Fill to switch first and last name in excel with comma

Note:

1. You can also choose the Flash Fill option from here:

Data tab => Data Tools group of commands => Flash Fill

2. Alternatively, you can use the keyboard shortcut Ctrl+E for Flash Fill.

 => Select cell C5 and input the name like this: Smith, Emily
 => Keeping cell C5 selected, press Ctrl+E on the keyboard. You will get the First Name and Last Name switched with a comma.


1.2 Using Flash Fill from the Fill Handle tool to Swich First Name and Last Name in Excel

In this step, we shall use Excel’s Flash Fill to switch names but we shall use Flash Fill with Excel’s other popular feature Fill Handle.

Follow these steps. We have the same dataset.

Step 1: Select cell C5 and input the name like this: Smith, Emily.

Using Flash Fill from Fill Handle

Step 2: Now I take my mouse over the bottom-right corner of the cell C5.

=> The cursor will be changed from the White Plus sign to Green Plus. This Green Plus is our Fill Handle feature.

=> Now click and drag the mouse until the mouse reaches the last row of the data

=> You will find the Auto Fill Options drop-down icon, click on the icon, you will get several options displayed. Choose the last one, Flash Fill. You will get the desired output. First Name Last Name to Last Name, First Name for the whole data.

Note: The Flash Fill method is not dynamic. If you change the original data, then the cells where you used Flash Fill will not be changed automatically. You have to reuse Flash Fill after changing any data.

For example, we have changed the cell B5 from Emily Smith to Emily Gomes. However, the C5 cell of the column, where we applied Flash Fill is not updated according to the changed data. If you add a new value to the original dataset, Flash Fill won’t work for the newly added data. Follow the image below:

Limitations of Flash Fill

Because of the static nature of the Flash Fill feature, it is not feasible for changeable data. We will use combinations of Excel functions to switch first and last name in Excel with comma. If you apply the following formulas, your data will be updated automatically, whenever you make any changes to the original data.


2. Using Excel Formulas to Switch First and Last Name in Excel with Comma

Using Excel formulas is definitely a dynamic fix to switch first and last name with comma.

Excel doesn’t have any specific function that will switch the first and last names with a comma. So, we shall use combined functions. We shall use functions like RIGHT, LEFT, LEN, MID, SEARCH, FIND, SUBSTITUTE, CONCAT, REPLACE, etc. to do this.

Here, you will learn 9 formulas to switch first and last name in Excel with comma. Each formula will contain combined functions. Let’s start with using RIGHT, LEN, SEARCH, and LEFT functions to create a formula.


2.1 Combining RIGHT, LEN, SEARCH, and LEFT Functions

In this section, we shall combine the RIGHT, LEN, SEARCH, and LEFT text functions to switch first and last names with commas. These functions will work together to return the desired result.

Follow the steps:

Step 1: Write this formula in cell C5 and press the Enter button.

=RIGHT(B5,LEN(B5)-SEARCH(" ",B5))&", "&LEFT(B5,SEARCH(" ",B5)-1)

You will get the desired output: Smith, Emily in cell C5.

Using Excel formula to switch first and last name in Excel with comma

Step 2: Take the mouse over the bottom-right corner of the cell C5.

=> The cursor will be changed from the White Plus sign to Green Plus. This Green Plus is our Fill Handle feature.

Drag the Fill Handle and AutoFill the formulas up to the last row of data.

Using Excel formula to reverse names

We have switched the first and last names with commas for the rest of the cells.

Using Excel formula to switch first and last name in Excel with comma

Let’s explain how this formula works:

Formula Breakdown

=RIGHT(B5,LEN(B5)-SEARCH(” “,B5))&”, “&LEFT(B5,SEARCH(” “,B5)-1)

=RIGHT(B5,LEN(B5)-SEARCH(” “,B5))&”, “&LEFT(B5,6-1)// SEARCH(” “,B5) returns 6 because it searched for a space in cell B5 and the space is found at the 6th position.

=RIGHT(B5,LEN(B5)-SEARCH(” “,B5))&”, “&“Emily”//LEFT(B5,6-1) returns “Emily” because in cell B5, the first 5 (6-1=5) characters from left is “Emily”

=RIGHT(B5,LEN(B5)6)&”, “&”Emily”// SEARCH(” “,B5) returns 6 because it searched for a space in cell B5 and it is found at the 6th position.

=RIGHT(B5,11-6)&”, “&”Emily”// LEN(B5) returns 11 because the length of the cell B5 is 11 including the space.

=“Smith”&”, “&”Emily”// RIGHT(B5,11-6) returns “Smith” because in cell B5, the first 5 (11-6=5) characters from right is “Smith”

=Smith, Emily // the ampersand signs join the results with a comma between them.

Note: Be aware of the extra spaces in the original dataset. This formula works by searching a single space between the first and last name. So, if your data contains more than one space between the first and last names, it will give an incorrect result. You can use the TRIM function to eliminate the extra spaces. Just use TRIM(B5) instead of B5 in the above formula.


2.2 Using MID, SEARCH, and LEFT Functions

In this section, we shall combine the MID, SEARCH, and LEFT text functions to switch first and last names with commas. These functions will work together to return the correct result.

Follow the steps:

Step 1: Copy this formula in cell C5 and hit the Enter key.

=MID(B5,SEARCH(" ",B5)+1,30)&", "&LEFT(B5,SEARCH(" ",B5)-1)

Right after pressing Enter, you will get the output: Smith, Emily in cell C5. Here, the first and last name will switch with a comma between them.

Using Excel formula to reverse names

Step 2: To get the results for the other cells, drag the Fill Handle and AutoFill the formulas up to the last row of data.

Therefore, you will get the results for rest of the cells.

Using Excel formula to switch first and last name in Excel with comma

Formula Breakdown

=MID(B5,SEARCH(” “,B5)+1,30)&”, “&LEFT(B5,SEARCH(” “,B5)-1)

=MID(B5,SEARCH(” “,B5)+1,30)&”, “&LEFT(B5,6-1) // SEARCH(” “,B5) returns 6 because it searched for a single space in cell B5 and it found the space at the 6th position.

=MID(B5,SEARCH(” “,B5)+1,30)&”, “&“Emily”// LEFT(B5,6-1) returns “Emily” because in cell B5, the first 5 (6-1=5) characters from left is “Emily”

=MID(B5,6+1,30)&”, “&”Emily”// SEARCH(” “,B5) returns 6 because it searched for a space in cell B5 and it is found at the 6th position.

=“Smith”&”, “&”Emily”// MID(B5,6+1,30) returns “Smith” because it returns the strings of cell B5 starting from the 7th (6+1=7) position up to 30th position. As we want the strings up to the last character of cell B5, we provided an assumed number 30 here.

=Smith, Emily // the ampersand signs join the results with a comma between them.


2.3 Joining MID, FIND, and LEN Functions

Joining MID, FIND, and LEN text functions is an easy yet dynamic fix to switch the first and last names with commas.

Follow the steps:

Step 1: Write this formula in cell C5 and click on the Enter key.

=MID(B5&", "&B5,FIND(" ",B5)+1,LEN(B5)+1)

The result will be: Smith, Emily in cell C5. You will get the first and last name switched with a comma.

Using Excel formula to reverse names

Step 2: Now, to get the outputs for the other cells, drag the Fill Handle and AutoFill the formulas up to the last row of data.

You will get the first and last names switched with a comma for the entire dataset.

Using Excel formula to switch first and last name in Excel with comma

Formula Breakdown

=MID(B5&”, “&B5,FIND(” “,B5)+1,LEN(B5)+1)

=MID(B5&”, “&B5,FIND(” “,B5)+1,11+1)// LEN(B5) returns 11 because the length of the cell B5 is 11 including the space.

=MID(B5&”, “&B5,6+1,11+1)// FIND(” “,B5) returns 6 because the FIND functions found a single space in cell B5 at the 6th position.

=Smith, Emily// MID(B5&”, “&B5,6+1,11+1) returns “Smith, Emily” because the MID function concatenated the value of B5 cell with a comma and a space with the same value of cell B5 from 7th (6+1=7) to 12th (11+1=12) position.


2.4 Combining MID, SEARCH, and LEN Functions

In this portion, we shall combine the MID, SEARCH and LEN functions to switch the first and last name in Excel without a comma.

For example, Smith, Emily will be Emily Smith.

Follow the steps:

Step 1: Copy this formula in cell C5 and hit the Enter button.

=MID(B5&" "&B5,SEARCH(", ",B5)+2,LEN(B5)-1)

Therefore, you will get the desired output in cell C5: Smith Emily

Using Excel formula to reverse names

Step 2: To switch the first and last names without commas for the rest of the cells, drag the Fill Handle and AutoFill the formulas up to the last row of data.

So, you will get the results for the entire dataset.

Using Excel formula to switch first and last name in Excel with comma

Formula Breakdown

=MID(B5&” “&B5,SEARCH(“, “,B5)+2,LEN(B5)-1)

=MID(B5&” “&B5,SEARCH(“, “,B5)+2,12-1)// LEN(B5) returns 12 because the length of the cell B5 is 12 including the comma and the space.

=MID(B5&” “&B5,6+2,12-1)// SEARCH(“, “,B5) returns 6 because it searched for a comma followed by a space in cell B5 and found it at 6th position.

=Smith Emily// MID(B5&” “&B5,6+2,12-1) returns Smith Emily because the MID function concatenated the value of B5 with a space and with the value of cell B5 from 8th (6+2=8) to 11th (12-1=11) position.


2.5 Using REPLACE, SEARCH, and LEFT Functions

In this section, we shall use the REPLACE, SEARCH, and LEFT text functions to switch first and last name in Excel without comma.

Let’s follow the steps:

Step 1: Write this formula in cell C5 and press Enter.

=REPLACE(B5,1,SEARCH(",",B5)+1,"")&" "&LEFT(B5,SEARCH(",",B5)-1)

Therefore, you will get the first and last names switched without comma. In other words, you will get: Smith Emily in cell C5.

Using Excel formula to switch first and last name in Excel with comma

Step 2: Drag the Fill Handle and AutoFill the formulas up to the last row of data. You will get the desired outputs for the rest of the cells.

Using Excel formula to switch first and last name in Excel with comma

Formula Breakdown

=REPLACE(B5,1,SEARCH(“,”,B5)+1,””)&” “&LEFT(B5,SEARCH(“,”,B5)-1)

=REPLACE(B5,1,SEARCH(“,”,B5)+1,””)&” “&LEFT(B5,6-1)// SEARCH(“, “,B5) returns 6 because it searched for a comma in cell B5 and found it at 6th position.

=REPLACE(B5,1,SEARCH(“,”,B5)+1,””)&” “&“Emily”//  LEFT(B5,6-1) returns “Emily” because in cell B5, the first 5 (6-1=5) characters from left is “Emily”.

=REPLACE(B5,1,6+1,””)&” “&”Emily” // SEARCH(“,”,B5)+1 returns 6 because the position of the space in cell B5 is found at 6th position.

=Smith&” “&”Emily”// REPLACE(B5,1,6+1,””) returns “Smith” because it replaces 1st to 7th (6+1=7) character of cell B5 with no space (“”). If  “Emily, ” is replaced with no space, “Smith” will remain.

=Smith Emily// the ampersand signs join the results with a comma between them.


2.6 Combining CONCAT, RIGHT, LEN, FIND, and LEFT Functions

We shall combine the CONCAT, RIGHT, LEN, FIND, and LEFT functions to reverse the first and last name in Excel with comma.

Follow the steps:

Step 1: Write this formula in cell C5.

=CONCAT(RIGHT(B5,LEN(B5)-FIND(" ",B5)),", ",LEFT(B5,FIND(" ",B5)-1))

Press the Enter button and you will get the output: Smith, Emily in cell C5.

Using Excel formula to switch first and last name in Excel with comma

Step 2: Now, drag the Fill Handle and AutoFill the formulas up to the last row of data. Thus you will switch the first and last names with comma for all cells.

Using Excel formula to switch first and last name in Excel with comma

Formula Breakdown

=CONCAT(RIGHT(B5,LEN(B5)-FIND(” “,B5)),”, “,LEFT(B5,FIND(” “,B5)-1))

=CONCAT(RIGHT(B5,LEN(B5)-FIND(” “,B5)),”, “,LEFT(B5,6-1))// FIND(” “,B5) returns 6 because the FIND functions found a single space in cell B5 at the 6th position.

=CONCAT(RIGHT(B5,LEN(B5)-FIND(” “,B5)),”, “,”Emily“)// LEFT(B5,6-1) returns “Emily” because in cell B5, the first 5 (6-1=5) characters from left is “Emily”.

=CONCAT(RIGHT(B5,LEN(B5)6),”, “,”Emily”)// FIND(” “,B5) returns 6 because the FIND functions found a single space in cell B5 at the 6th position.

=CONCAT(RIGHT(B5,11-6),”, “,”Emily”)// LEN(B5) returns 11 because the length of the cell B5 is 11 including the space.

=CONCAT(“Smith“,”, “,”Emily”)// RIGHT(B5,11-6) returns “Smith” because in cell B5, the first 5 (11-6=5) characters from right is “Smith”

=Smith, Emily// CONCAT(“Smith”,”, “,”Emily”) returns Smith, Emily because it concatenated the values “Smith”, “, ”, and “Emily”.


2.7 Joining CONCAT, TEXTAFTER, and TEXTBEFORE Functions

In this section, we shall join the CONCAT, TEXTAFTER, and TEXTBEFORE functions to switch the first and last names with comma.

Note: The TEXTAFTER and TEXTBEFORE functions are only available in the Microsoft 365 version.

Let’s follow the steps:

Step 1: Copy this formula in cell C5 and hit the Enter button.

=CONCAT(TEXTAFTER(B5," "), ", ",TEXTBEFORE(B5, " "))

Thus you will get the first and last names switched with a comma: Smith, Emily in cell C5.

Using combined Excel formula

Step 2: To get the values for the rest of the cells, drag the Fill Handle and AutoFill the formulas up to the last row of data.

You will get the outputs for the rest of the cells.

Using Excel formula to switch first and last name in Excel with comma

Formula Breakdown

=CONCAT(TEXTAFTER(B5,” “), “, “,TEXTBEFORE(B5, ” “))

=CONCAT(TEXTAFTER(B5,” “), “, “,”Emily“)// TEXTBEFORE(B5, ” “) returns “Emily” because the text before a space in cell B5 is “Emily”.

=CONCAT(“Smith, “, “,”Emily”)// TEXTAFTER(B5,” “) returns “Smith” because the text after a comma followed by a space in cell B5 is “Smith”.

=Smith, Emily// CONCAT(“Smith”, “, “,”Emily”) returns “Smith, Emily” because it concatenated the values “Smith”, “, ” and “Emily”.


2.8 Using RIGHT, LEFT, LEN, SEARCH, SUBSTITUTE, and CONCAT Function (If Middle Name is Present)

In this section, we shall learn how to switch first and last name with comma if the names contain middle names also. We shall combine the RIGHT, LEFT, LEN, SEARCH, SUBSTITUTE, and CONCAT functions to do this task.

First, we shall extract the last name and first name in C and D columns by using combined functions. Then, by using the CONCAT function, we shall join the last name and first name with a comma.

We shall use the following dataset.

Using Excel formula to switch first and last name in Excel with comma

Follow the steps:

Step 1: To get the last names, write this formula in cell C5 and press Enter.

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5," ","#",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))

You will get the last name: Smith in cell C5.

Using combined Excel formula

Step 2: Drag the Fill Handle and AutoFill the formulas up to the last row of data.

Therefore, you will get the last names for the entire dataset.

Using Excel formula to switch first and last name in Excel with comma

Formula Breakdown

=RIGHT(B5,LEN(B5)-SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)))))

=RIGHT(B5,LEN(B5)-SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(“EmilyM.Smith“))))// SUBSTITUTE(B5,” “,””) returns EmilyM.Smith because it substituted the spaces of cell B5 with no space.

=RIGHT(B5,LEN(B5)-SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,LEN(B5)12)))// LEN(“EmilyM.Smith”) returns 12 since the length of “EmilyM.Smith” is 12.

=RIGHT(B5,LEN(B5)-SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,14-12)))// LEN(B5) returns 14 because the length of the characters of cell B5 is 14 including the spaces.

=RIGHT(B5,LEN(B5)-SEARCH(“#”,“Emily M.#Smith”))// SUBSTITUTE(B5,” “,”#”,14-12) returns “Emily M.#Smith” because it substituted the space with “#” at 2nd (14-12=2) instance.

=RIGHT(B5,LEN(B5)9)// SEARCH(“#”,”Emily M.#Smith”) returns 9 because it searched the position of “#” and it is at 9th position.

=RIGHT(B5,14-9)// LEN(B5) returns 14 because it is the length of cell B5.

=Smith// RIGHT(B5,14-9) returns “Smith” because from right, “Smith” is the 5 (14-9=5) strings.

Step 3: To get the first names, write this formula in cell D5 and press Enter.

=LEFT(B5,SEARCH(" ",B5)-1)

Thus you will get the first name: Emily in cell D5.

Using combined Excel formula

Step 4: Drag the Fill Handle and AutoFill the formulas up to last row of data.

You will get the first names for the rest of the cells.

Using Excel formula to switch first and last name in Excel with comma

Formula Breakdown

=LEFT(B5,SEARCH(” “,B5)-1)

=LEFT(B5,6-1)// SEARCH(” “,B5) returns 6 because the first space is at 6th position in cell B5.

=Emily// LEFT(B5,6-1) returns “Emily” because it is the first 5 (6-1=5) characters from left in cell B5.

Step 5: Now, we shall join the last and first name with comma. Write this formula in cell E5 and press Enter.

=CONCAT(C5,", ",D5)

You will get the output: Smith, Emily in cell E5. So, the first and last name is switched with comma.

Using combined Excel formula

Step 6: Drag the Fill Handle and AutoFill the formulas up to last row of data.

Therefore, you will get the output for the rest of the cells.

Using Excel formula to switch first and last name in Excel with comma


2.9 Using TEXTSPLIT Function with Other Functions

In this portion, we shall combine the TEXTSPLIT function with other functions TEXTJOIN, SORTBY, TRANSPOSE, SEQUENCE, and COUNTA to switch first and last name in Excel with comma.

We shall use the same dataset. Follow the steps:

Step 1: Write this formula in cell C5 and hit the Enter button.

=TEXTJOIN(", ",TRUE,SORTBY(TRANSPOSE(TEXTSPLIT(B5," ")),SEQUENCE(COUNTA(TEXTSPLIT(B5," "))),-1))

Thus, you will get the output: Smith, Emily in cell C5.

Using combined Excel formula

Step 2: Drag the Fill Handle and AutoFill the formulas up to last row of data.

You will get the first and last name switched with comma in other cells.

Using Excel formula to switch first and last name in Excel with comma

Formula Breakdown

=TEXTJOIN(“, “,TRUE,SORTBY(TRANSPOSE(TEXTSPLIT(B5,” “)),SEQUENCE(COUNTA(TEXTSPLIT(B5,” “))),-1))

=TEXTJOIN(“, “,TRUE,SORTBY(TRANSPOSE(TEXTSPLIT(B5,” “)),SEQUENCE(COUNTA({“Emily”,”Smith”})),-1))// TEXTSPLIT(B5,” “) returns the array {“Emily”,“Smith”} because it split the value of B5 across columns where it found the space delimiter.

=TEXTJOIN(“, “,TRUE,SORTBY(TRANSPOSE(TEXTSPLIT(B5,” “)),SEQUENCE(2),-1))// COUNTA({“Emily”,”Smith”}) returns 2 because there are 2 non blank cells.

=TEXTJOIN(“, “,TRUE,SORTBY(TRANSPOSE(TEXTSPLIT(B5,” “)),{1;2},-1))// SEQUENCE(2) returns {1;2} because it generated 2 sequential numbers 1 and 2.

=TEXTJOIN(“, “,TRUE,SORTBY(TRANSPOSE({“Emily”,”Smith”}),{1;2},-1))// TEXTSPLIT(B5,” “) returns the array {“Emily”,“Smith”} because it split the value of B5 across columns where it found a space.

=TEXTJOIN(“, “,TRUE,SORTBY({“Emily”;”Smith”},{1;2},-1))// TRANSPOSE({“Emily”,”Smith”}) returns {“Emily”;”Smith”} because changed it from a horizontal arrangement to a vertical arrangement.

=TEXTJOIN(“, “,TRUE,{“Smith”;”Emily”})// SORTBY({“Emily”;”Smith”},{1;2},-1) returns {“Smith”;”Emily”} since it sorted the array with 2 elements in descending order.

=Smith, Emily// TEXTJOIN(“, “,TRUE,{“Smith”;”Emily”}) returns “Smith, Emily” because it joined the values with comma delimiter.


3. Using Convert Text to Columns Wizard and Excel CONCAT Function to Switch First and Last Name in Excel with Comma

Splitting First Name and Last Names into two parts using Convert Text to Columns Wizard and then joining those parts with commas using Excel CONCAT functions, you can switch first name and last name in Excel with commas.

Excel’s Convert Text to Columns Wizard feature splits the content of one cell into multiple cells using the delimiters (comma, space, semicolon, etc.) you choose.

In this method, we shall use the Convert Text to Columns Wizard to split column B into columns C and D. It will separate the first and last names. Then we shall use the CONCAT function in column E to join the last name and first name with a comma and a space.

This is the dataset I am going use.

 Dataset for using Text to Column Wizard

Follow the steps:

Step 1: Select the range B5:B9.

=> Click on the Data tab => Data Tools group of commands => Click on the Text to Columns command.

using Text to Column Wizard to switch first and last name in Excel with comma

Step 2: The Convert Text to Columns Wizard will appear. It is Step 1 of 3. The text wizard has detected correctly that our data is Delimited. So, click on Next.

Convert Text to Columns Wizard

Step 3: In Step 2 of 3, check the option Space under the field Delimiters => Click on the Next option.

Convert Text to Columns Wizard

Step 4: In the final step, select the General option under the Column data format field => Select the cell location (C5, it is the first cell of the First Name column) where you want to get the values under the Destination field => Click on Finish.

Convert Text to Columns Wizard

 When you click on Finish, the text data of column B is split into columns C and D perfectly.

using Text to Column Wizard to switch first and last name in Excel with comma

Now, we shall use the CONCAT function to join the Last Name and First Name columns with a comma and a space.

Step 5: Write this formula in cell E5 and hit the Enter button.

=CONCAT(D5,”,”,C5)

You will get the first and last names switched with a comma.

using Text to Column Wizard to switch first and last name in Excel with comma

Step 6: Use the AutoFill feature to fill the formula for the rest of the cells.

using Text to Column Wizard to switch first and last name in Excel with comma

Read More: How to Reverse Names in Excel


4. Using Power Query to Switch First and Last Name in Excel with Comma

In this section, you will learn how to switch first name and last name in Excel using Excel’s Power Query tool.

If you have to switch first and last names with comma quite often, it wouldn’t be a good choice to use Flash Fill or formulas each time. You can use the Power Query to switch first and last name with comma. Then you can use the same query multiple times with various datasets.

We shall use the following dataset. It contains 10 first and last names.

Dataset for using power query tool to switch first and last name in Excel with comma

Follow the steps:

Step 1: Select the dataset including the header => Click on the Data tab => Get & Transform Data group of commands => Select From Table/Range option.

using power query tool to switch first and last name in Excel with comma

Step 2: You will get a Create Table dialog box. Under where is the data for your table? field, the selected data range is showing. As you have selected the dataset including the header, My table has headers option is checked automatically.

=>Press the OK button.

using power query tool to switch first and last name in Excel with comma

After clicking OK, you will get a table in Power Query Editor created with the selected range.

using power query tool to switch first and last name in Excel with comma

Step 3: In Power Query Editor, click on the Add Column tab =>General group of commands, select the Column From Examples option.

using power query tool to switch first and last name in Excel with comma

Step 4: A new column named Column1 is added. You have to set examples in this column so that the Power Query can detect the pattern.

=> Write Johnson, Sarah in the first row of the column.

using power query tool to switch first and last name in Excel with comma

Step 5: Give another example in the second row “Smith, Michael” and press Enter.

After pressing Enter, the other cells will show suggestions. The suggestions are correct so you don’t have to provide any more examples.

Note: Check the suggestions properly. If it doesn’t match your requirements, you have to give more examples. Keep providing examples until it matches your pattern properly.

using power query tool to switch first and last name in Excel with comma

Step 6: Press the OK button or just use the keyboard shortcut Ctrl+Enter to apply the suggested values.

Note: It is not mandatory to provide examples from the first row of the column. You can start providing the examples from any row of the Custom Column.

using power query tool to switch first and last name in Excel with comma

Thus, you will get the first and last names switched with commas.

using power query tool to switch first and last name in Excel with comma

Step 7: Double-click on the column header and rename it according to your preference.

using power query tool to switch first and last name in Excel with comma

Step 8: Right-click on the first column =>Select Remove.

using power query tool to switch first and last name in Excel with comma

Step 9: The first column is removed. To load the Last Name, First Name column in the Excel worksheet,

=>Click on the Home tab => Select Close & Load dropdown => Click on the Close & Load To option.

using power query tool to switch first and last name in Excel with comma

Step 10: The Import Data dialog box will appear. The Table option is selected by default.

=>Select Existing Worksheet.

=>Select the cell where you want to load the table.

=>Click on the OK button.

using power query tool to switch first and last name in Excel with comma

As a result, you will get the switched first and last names in your worksheet.

using power query tool to switch first and last name in Excel with comma


5. Using Power Pivot

In this section, we shall use the Power Pivot add-in of Excel to switch first and last name in Excel with comma. Power Pivot is a powerful data analysis tool. It is available as an add-in for Microsoft Excel.

In Power Pivot, we shall add a calculated column where we will use DAX (Data Analysis Expressions) formula to switch the first and last name with comma. Later we shall present that column with a PivotTable.

We shall use the same dataset. Our goal is to switch the first and last names of column B with commas.

Dataset for using power pivot to switch first and last name in Excel with comma

Follow the steps:

Step 1: Select the entire range where you have your dataset =>Click on the Power Pivot tab =>Select the Add to Data Model option.

Note: In case, you don’t have the Power Pivot tab, enable the Power Pivot add-in first.

using power pivot to switch first and last name in Excel with comma

Step 2: Create Table dialog box will appear. As we have selected the range before, it is showing already. Press the OK button.

using power pivot to switch first and last name in Excel with comma

You will see the selected column in Power Pivot window.

using power pivot to switch first and last name in Excel with comma

Step 3: To rename the table name, Double-click on the table name which is set as Table1 by default.

using power pivot to switch first and last name in Excel with comma

Step 4: Rename this table according to your choice. Try to give a relevant name.

We renamed it as Switch_Names.

using power pivot to switch first and last name in Excel with comma

Step 5: Click on Add Column.

using power pivot to switch first and last name in Excel with comma

Step 6: A new column is added. Write this DAX formula in the formula bar of the newly added column and press Enter.

=RIGHT( Switch_Names[First Name Last Name] , LEN( Switch_Names[First Name Last Name] ) - FIND( " " , Switch_Names[First Name Last Name] ) ) & ", " & LEFT( Switch_Names[First Name Last Name] , FIND( " " , Switch_Names[First Name Last Name] ) - 1 )

Therefore, you will get the new calculated column. Here, the first and last names are switched with commas.

using DAX functions in power pivot to switch first and last name in Excel with comma

Note: This formula is similar to the formula we used in Excel worksheet using text functions. Power Pivot has a formula language known as DAX. The difference is Excel formulas work for a single cell at a time. But DAX formula works for the entire column.

Step 7: Double-click on the column header and rename it. We have renamed it as Last Name, First Name.

Using DAX formula in power pivot to switch first and last name in Excel with comma

Step 8: Select the calculated column =>Click on the Home tab =>Select the PivotTable dropdown =>Select the PivotTable option.

using power pivot to switch first and last name in Excel with comma

Step 9: The Create PivotTable dialog box will appear. The option New worksheet is selected by default. So, press OK.

using power pivot to switch first and last name in Excel with comma

You will see, a new Excel worksheet is opened and you have to choose the fields from the PivotTable field list.

using power pivot to switch first and last name in Excel with comma

Step 10: Choose the field Last Name, First Name from the Switch_Names table.

using power pivot to switch first and last name in Excel with comma

We have created the PivotTable where we get the first and last names switched with commas.


6. Applying VBA Code to Switch First and Last Name in Excel with Comma

In this section, you will learn applying VBA code to switch first and last name in Excel with comma.

If you have to switch first and last names with commas multiple times in a workbook, creating a VBA code for this action is the best solution. You can use this VBA code for multiple datasets. It will save time and effort.

We shall use the following dataset to run the VBA code.

Dataset for applying VBA code

Follow the steps:

Step 1: To write any VBA code, you must launch the VBA editor first. To do that,

=>Click on the Developer tab from Excel Ribbon. If you don’t have the Developer tab in Excel Ribbon, you have to enable it from Excel Options.

=>Afterward, select the Visual Basic option.

Note: Alternatively, you can press Alt+F11 to open the VBA editor.

Developer tab of ExcelStep 2: After clicking on Visual Basic, Excel will lead you to the VBA Editor Window. In this window, you can write your VBA code.

VBA code editor

Step 3: Now, select Insert => Click on Module.

Inserting module

Step 4: You have inserted a new module. Now, copy this code to the module and save the sub procedure.

Sub Switch_first_and_last_name()
Dim Space_position As Long
    For Each Cell In Selection
    Space_position = InStr(Cell.Value, " ")
        If Space_position > 0 Then
            Cell.Value = Trim$(Mid$(Cell.Value, Space_position + 1)) _
            & ", " & Left$(Cell.Value, Space_position - 1)
        End If
    Next Cell
End Sub

VBA code to switch first and last name in Excel with commaVBA code to switch first and last name in Excel with comma

Note: Don’t forget to save your workbook as a Micro-Enabled Workbook.

Step 5: Go back to the Excel worksheet and select the range for which you want to switch first and last name with comma.

=>Select the Developer tab => Code group of commands => Select the Visual Basic option.

Applying VBA code to switch first and last name in Excel with comma

Step 6: Click on Run => Run Macro or just use the keyboard shortcut F5 to run the code.

running VBA code to switch first and last name in Excel with comma

Step 7: Right after running the code the first and last names are switched with comma. Go to the Excek worksheet and you will notice the changes. The first and last names are switched with commas successfully.

Applying VBA code to switch first and last name in Excel with comma

Note: If you run the VBA code once, you cannot undo this action. So it is a good idea to store the backup copy of the original dataset before running the VBA macro. In case, you need the original version you can get it.

Note: You can save this VBA code in your personal macro enabled workbook. Then, add the macro to the quick access toolbar with a relevant icon. By doing this, you can use this code multiple times just by clicking on the macro icon from the quick access toolbar. It will definitely save your time and effort.


Download Practice Workbook


Conclusion

In this Excel tutorial, you have learned how to switch first and last name in Excel with comma. For example, we showed to reverse Emily Smith to Smith, Emily. You can switch first and last name with comma in several ways. The easiest method is using the Flash Fill feature. But as it is not a dynamic fix, we showed other ways like using formulas, using Convert Text to Columns Wizard, using Power Query, using Power Pivot, and applying Excel VBA code. We hope, from this tutorial, you have learned how to switch first and last name in Excel with comma.


Related Readings


<< Go Back to Excel Reverse Order | Sort in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

4 Comments
  1. Hiya – great article – could you help with the following please ?
    My list of names isn’t consistent in terms of number of forenames as I have people from the UK and from Singapore in the list I am manipulating.
    For the UK – the majority are surname,forename so I can use your method above.
    For Singapore I have:

    Surname, forename2 forename1
    or
    Surname, forename2 forename3 forename1
    (only one comma in the string)

    I would like this to be
    Forename1 Forename2 (forename3) Surname
    Is this possibe ?
    Many thanks in advance for your help

    • Hi, GEOFF BARTLETT! We appreciate your thoughtful query.
      Workaround 1:
      For your first problem (Surname, forename2 forename1), you can use the formula below:
      =SUBSTITUTE((RIGHT($B7,LEN($B7)-FIND("^",SUBSTITUTE($B7," ","^",LEN($B7)-LEN(SUBSTITUTE($B7," ",""))))))&" "&(MID($B7,SEARCH(" ",$B7)+1,SEARCH(" ",$B7,SEARCH(" ",$B7)+1)-(SEARCH(" ",$B7)+1)))&" "&(LEFT($B7,SEARCH(" ",$B7)-1)),",", "")

      And, for your second problem (Surname, forename2 forename3 forename1) you can use the formula below:
      =SUBSTITUTE((RIGHT($B5,LEN($B5)-FIND("^",SUBSTITUTE($B5," ","^",LEN($B5)-LEN(SUBSTITUTE($B5," ",""))))))&" "&(MID($B5,SEARCH(" ",$B5)+1,SEARCH(" ",$B5,SEARCH(" ",$B5)+1)-(SEARCH(" ",$B5)+1)))&" "&"("&(MID($B5,SEARCH(" ",$B5,SEARCH(" ",$B5,1)+1)+1,SEARCH(" ",$B5,SEARCH(" ",$B5,SEARCH(" ",$B5,1)+1)+1)-(SEARCH(" ",$B5,SEARCH(" ",$B5,1)+1)+1)))&")"&" "&(LEFT($B5,SEARCH(" ",$B5)-1)),",", "")

      Workaround 2:
      Besides, another workflow you can use in this regard. That is:
      Step 1: Use the Text to Columns tool from the Data tab for splitting every name.
      https://www.exceldemy.com/text-to-columns-excel/
      Switch Text to Columns

      Convert Text to Columns Wizard- Step 1 of 3

      Convert Text to Columns Wizard- Step 2 of 3

      Convert Text to Columns Wizard- Step 3 of 3

      Step 2: Sort them according to your desired sequence. Use a helper row for that. Use and finally >>
      Access the Sort Feature

      Sort Options Window

      Sort by Helper Row

      Last Step: Use the CONCATENATE function to combine them in a cell.
      https://www.exceldemy.com/excel-concatenate-function/
      CONCATENATE Function to Combine Sorted Results

      Regards,
      Tanjim Reza

  2. Hello are you presentto help

    Wish to make John Smith > Smith J

    Thank you.

    • Hi, JUSTIN!
      Thank you for your query.
      You can accomplish your desired result by using the formula below:

      =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&" "&LEFT(A2,1)

      Regards,
      Tanjim Reza

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo