Reverse last name, first name in excel with comma

Today’s blog post was actually a question from one of my colleagues at work earlier this week, they had a list of first and surnames of employees, separated by a comma, with the surname first and the first names last and they needed to switch them around for example ,from “Green, John” to” John Green”.

Table of Contents Show

Method 1. Text To Column & TextJoin
Method 2. Use A One Stop Shop Formula
How do you reverse first and last name with commas?
How do you flip last name and first name in Excel?
How do I rearrange names in Excel with a comma?
How do you reverse a comma in Excel?

So let’s look at two of the ways we could easily do this in Excel.

The first method involves 2 steps.

Method 1. Text To Column & TextJoin

First we can use Text To Columns to split the First Name and Last Name.

Select the column that contains your data
Data Tab – Data Tools – Text To Columns
In Step 1 of the Wizard select Delimited
We can use comma as the delimiter
Hit Next, Then confirm to Finish
Your data will now be split, having used the comma as the delimiter

Once we have our two names separated, we can then join them together again using TEXTJOIN. This is one of the new formulas that cam with Excel 2016. If you don’t have TEXTJOIN then you can use CONCATENATE which works in previous versions of Excel prior to 2016. You can read my blog posts below, if you want to use CONCATENATE.

How To Concatenate Numbers And Percentages

Formula Friday – Let’s Use TEXTJOIN To Join Text Strings Without The CONCATENATE Complexity

Understand The Power Of Concatenation Of Cell Formulas

I have Excel 2016 so I’m going to jump right in and use TEXTJOIN, the updated and more simplified relative of CONCATENATE.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Where

delimiter – this is required this is what you want in between the cells that you are joining, for example- a comma, or a space.

ignore_empty – this is so you can choose to ignore and empty cells in your selected range. This can be important if you are using a space " " as your delimter and if you do not want extra spaces in between your joined text strings due to blank spaces in your selected cells.

text1,-is required, which can be a single text string or an array of strings like a range of cells

[text2] – this is an optional argument; use this additional text strings to be added to text1

So, in our example we want the delimeter to be a space, to separate the two names, we want to ignore any extra spaces so we set the ignore_empty to TRUE and select text1 as our First Name (cell C2) and text2 as B2 to populate our Second Name.

Hit Ok and we have our names switched to the correct way around.

Method 2. Use A One Stop Shop Formula

Ok so we have seen a two step approach to reversing the names, let do it all all together with one formula.

The formula uses the functions MID, FIND and LEN.

MID – this returns the middle characters from a text string given a starting and end position.

FIND – this finds the starting position of one text string within another text string.

LEN – returns the number of characters in a text string

Once we put all of these together as one formula, we can easily switch the positions of the two names,

This is a great formula…..if we break it down we can see the way it works.

Excel uses the MID function, which as we know returns the middle characters from a text string given a start number and length. So, we generate the Text piece by joining B2 and B2 again separated by a space, this gives us the following ‘Green,John Green,John’

=MID(B2&” “&B2

We find the start number by finding the position of the comma, then adding +1

=MID(B2&” “&B2,FIND(“,”,B2)+1

We find the number of characters to then return is the number of characters in cell B2

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

Clever huh?. We can then simply drag our formula down the column of data to correctly fill the rest of our names.

So there are two ways to flip the cell contents in Excel. Which would you use????

Don’t forget to sign up to the Excel at Excel Newsletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.

How do you reverse first and last name with commas?

In the Replace With control, enter the following characters \2, \1, with a space character before the second slash character. Click Replace All. Word will transpose the first and last names and separate them with a comma character.