In the following image, we have switched the first and last name in Excel with comma.

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.

## Method 1 – Using Flash Fill to Switch the First and Last Name in Excel with a Comma

### Case 1.1 – Using Flash Fill from the Home Tab

**Steps:**

- Select cell
**C5**and input the name like this:**Smith, Emily.**

*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.*

- Go to the
**Home**tab. - Click on the
**Fill**dropdown. - Select the
**Flash Fill**command.

- You will get the
**First Name Last Name**switched to the format**Last Name, First Name**throughout the column.

*Notes:*

1.* You can also choose the Flash Fill option from the Data tab.*

*2. Alternatively, you can use the keyboard shortcut Ctrl + E for Flash Fill. You need to have the range selected.*

### Case 1.2 – Using Flash Fill from the Fill Handle Tool

**Steps:**

- Select cell
**C5**and input the nam in the format you need**.**

- Hover 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. - Click and drag down until you reach the last row of the data
- Click on the
**Auto Fill Options**drop-down icon. - Choose the last option,
**Flash Fill**.

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

## Method 2 – Using Excel Formulas to Switch the First and Last Name in Excel with Comma

### Case 2.1 – Combining RIGHT, LEN, SEARCH, and LEFT Functions

**Steps: **

- Insert the following formula in cell
**C5**and press the**Enter**button.

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

- Drag the
**Fill Handle**down to**AutoFill**the formula.

** **

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

**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.*

### Case 2.2 – Using MID, SEARCH, and LEFT Functions

**Steps: **

- Insert the following formula in cell
**C5**and press the**Enter**button.

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

- Drag the
**Fill Handle**and**AutoFill**the formula down.

**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.

### Case 2.3 – Joining MID, FIND, and LEN Functions

**Steps: **

- Insert the following formula in cell
**C5**and press the**Enter**button.

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

- Drag the
**Fill Handle**to**AutoFill**the formula down.

**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.

### Case 2.4 – Combining MID, SEARCH, and LEN Functions

**Steps: **

- Insert the following formula in cell
**C5**and press the**Enter**button.

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

- Drag the
**Fill Handle**down and**AutoFill**the formula through the column.

**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.

### Case 2.5 – Using REPLACE, SEARCH, and LEFT Functions

**Steps: **

- Insert the following formula in cell
**C5**and press the**Enter**button.

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

- Drag the
**Fill Handle**down to**AutoFill**the formula.

**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.

### Case 2.6 – Combining CONCAT, RIGHT, LEN, FIND, and LEFT Functions

**Steps: **

- Insert the following formula in cell
**C5**and press the**Enter**button.

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

- Drag the
**Fill Handle**down to**AutoFill.**

**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”.

### Case 2.7 – Joining CONCAT, TEXTAFTER, and TEXTBEFORE Functions

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

**Steps: **

- Insert the following formula in cell
**C5**and press the**Enter**button.

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

- Drag the
**Fill Handle**down to**AutoFill**the column.

**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”.

### Case 2.8 – Using RIGHT, LEFT, LEN, SEARCH, SUBSTITUTE, and CONCAT Functions (If a Middle Name Is Present)

We have a middle name in the values, so we’ll extract the last name and first name in **C** and **D** columns by using combined functions. We’ll use those values to make the final result.

**Steps: **

- Insert the following formula in cell
**C5**and press the**Enter**button.

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

- Drag the
**Fill Handle**to**AutoFill**the formula down.

**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.

- Insert this formula in cell
**D5**and press**Enter**.

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

**AutoFill**the formula down.

**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.

- Use this formula in cell
**E5**and press**Enter**.

`=CONCAT(C5,", ",D5)`

- Drag the
**Fill Handle**down to**AutoFill.**

### Case 2.9 – Using the TEXTSPLIT Function with Other Functions

**Steps: **

- Insert the following formula in cell
**C5**and press the**Enter**button.

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

- Drag the
**Fill Handle**to**AutoFill**down.

**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.

## Method 3 – Using the Text to Columns Wizard and Excel CONCAT Function to Switch the First and Last Name in Excel with Comma

We’ll separate the first and last name then concatenate them with a comma.

**Steps: **

- Select the range
**B5:B9**. - Click on the
**Data**tab. - Click on the
**Text to Columns**command.

- The
**Convert Text to Columns Wizard**will appear with Step**1**of**3**. The text wizard should detect that the data is**Delimited**. - Click on
**Next**.

- In Step
**2**of**3**, check the option**Space**under the field**Delimiters**. - Click on the
**Next**option.

- Select the
**General**option under the**Column data format**field. - Select the cell location (
**C5**, the first cell of the**First Name**column) where you want to get the values under the**Destination**field. - Click on
**Finish**.

- The text data of column
**B**is split into columns**C**and**D**.

** **

- Use this formula in cell
**E5**and hit the**Enter**button.

`=CONCAT(D5,”,”,C5)`

- Use the
**AutoFill**feature to fill the formula for the rest of the cells.

**Read More: **How to Reverse Names in Excel

## Method 4 – Using Power Query to Switch the First and Last Name in Excel with Comma

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

**Steps:**

- Select the dataset including the headers.
- Click on the
**Data**tab and select**From Table/Range**.

- You will get a
**Create Table**dialog box. - Under
**where is the data for your table?**field, the selected data range is showing. - As we have selected the dataset including the header,
**My table has headers**option should be checked. - Press the
**OK**button.

- You will get a table in
**Power Query Editor**created with the selected range.

- In
**Power Query Editor**, click on the**Add Column**tab and select the**Column From Examples**option.

- A new column named
**Column1**is added. Set examples in this column so that the Power Query can detect the pattern. - Write
**Johnson, Sarah**in the first row of the column.

- Insert another example in the second row and press
**Enter**. - The other cells will show suggestions.

*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.*

- Press the
**OK**button or use the keyboard shortcut**Ctrl + Enter**to apply the suggested values.

*Note:** You can start providing the examples from any row of the Custom Column.*

* *

- You will get the first and last names switched with commas.

**Double-click**on the column header and rename it however you want.

**Right-click**on the first column and select**Remove**.

- Click on the
**Home**tab and select**Close & Load,**then choose the**Close & Load To**option.

- 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.

- You will get the switched first and last names in your worksheet.

## Method 5 – Using Power Pivot

We’ll use the same dataset.

**Steps: **

- Select the entire range where you have your datase.
- 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 in Options.*

- The
**Create Table**dialog box will appear. As we have selected the range before, it is showing already. - Press the
**OK**button.

- You will see the selected column in the Power Pivot window.

**Double-click**on the table name which is set as**Table1**by default.

- Rename this table. We renamed it as
**Switch_Names**.

- Click on
**Add Column.**

- Use 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 )`

*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.*

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

- Select the calculated column.
- Click on the
**Home**tab. - Select the
**PivotTable**dropdown. - Select the
**PivotTable**option.

- The
**Create PivotTable**dialog box will appear. The option**New worksheet**is selected by default. - Press
**OK**.

- A new Excel worksheet will open, and you have to choose the fields from the PivotTable field list.

- Choose the field
**Last Name, First Name**from the**Switch_Names**table.

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

## Method 6 – Applying VBA Code to Switch the First and Last Name in Excel with a Comma

Here’s the dataset we’ll use.

**Steps:**

- Click on the
**Developer**tab. If you don’t have the**Developer**tab in Excel Ribbon, you have to enable it from**Excel Options**. - Select the
**Visual Basic**option.

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

**Excel**will lead you to the**VBA Editor**Window.

- Select
**Insert**and click on**Module**.

- Copy the following code into the module and save the file.

```
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 comma

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

- 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. - Select the
**Visual Basic**option.

- Click on
**Run**and choose**Run Macro**or use the keyboard shortcut**F5**to run the code.

- Go to the Excel worksheet and you should get the results.

*Note: **If you run the VBA code once, you cannot undo this action. Store the backup copy of the original dataset before running the VBA macro.*

*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.*

**Download the Practice Workbook**

## Related Readings

- How to Reverse a String in Excel
- How to Use Excel VBA to Reverse String
- How to Reverse a Number in Excel
- How to Paste in Reverse Order in Excel
- How to Reverse Rows in Excel

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

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/

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

Last Step: Use the CONCATENATE function to combine them in a cell.

https://www.exceldemy.com/excel-concatenate-function/

Regards,

Tanjim Reza

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:

Regards,

Tanjim Reza