Switch First & Last Names w/ Commas – Excel & Google Sheets
Download the example workbook
This tutorial will demonstrate how to switch first and last names in Excel and Google Sheets.
Switch First and Last Names
Typically names are stored in one of two formats:
- Last Name, First Name
- First Name Last Name
Using Excel formulas we can switch the names around.
This formula will switch names from the first format to the second:
=MID(B3&" "&B3,SEARCH(" ",B3)+1,LEN(B3)-1)
We will walkthrough this below.
SEARCH Function
First, use the SEARCH function to output the position of the space within the name:
=SEARCH(" ", B3)+1
Add plus 1 to the formula to get the position of the beginning letter of the first name (skip the space).
LEN Function
The LEN Function will return the number of characters in a cell.
=LEN(B3)
MID Function
Next, use the MID Function to output text from the middle of the name. We will use our previous formulas to determine what text to output.
- The SEARCH Function returns the character to start with
- The LEN Function helps determine how many characters to return.
=MID(B3&" "&B3,C3,D3-1)
Combining these functions yields the original formula.
=MID(B3&" "&B3,SEARCH(" ",B3)+1,LEN(B3)-1)
Switch Last and First Names
You can also flip the text the other way and add in the comma using this formula:
=MID(B3&", "&B3,SEARCH(" ",B3)+1,LEN(B3)+1)
Switch First & Last Names w/ Commas in Google Sheets
These formulas work exactly the same in Google Sheets as in Excel.