Return to Excel Formulas List

Switch First & Last Names w/ Commas – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to switch first and last names in Excel and Google Sheets.

mf switch first last names commas

 

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)

switch first last names commas 01

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

switch first last names commas SEARCH

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)

switch first last names commas LEN

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)

switch first last names commas MID

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 commas FINAL

Switch First & Last Names w/ Commas in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.

switch first last names commas Google Function