Reverse Last Name & First Name With Comma Using One Formula: Google Sheets

Recently I had a column of names in a spreadsheet with the following structure: Last Name, First Name and they needed to change to the structure: First Name Last Name . For example, the original structure of someone’s name would be Smith, John and this needed to change to John Smith .

So a couple of medications were needed. First, fetch the respective names and identify them positionally according to their place on either side of the comma. Then, remove the comma and concatenate the names using a space character to separate them.

Here is the formula that works in Google Sheets if you need to reverse the format of the last name, comma, first name to first name, space, last name:

=CONCATENATE(TRIM(INDEX(SPLIT(A1,","), 2)), " ", TRIM(INDEX(SPLIT(A1,","), 1)))

Here’s how it looks when applied to some simple data:

	A	B
1	Smith, John	John Smith =CONCATENATE(TRIM(INDEX(SPLIT(A1,","),2))," ",TRIM(INDEX(SPLIT(A1,","), 1)))
2	Doe, Jane	Jane Doe =CONCATENATE(TRIM(INDEX(SPLIT(A2,","),2))," ",TRIM(INDEX(SPLIT(A2,", "), 1)))

How Does The Formula Work?

If you want to explore a little more on how the formula works then this may help you understand and make any modifications should there be fringe cases you need to deal with.

When decoding most formulas you want to try and start with the innermost formulas first, and then gradually make your way out.

SPLIT

The first task required is to split the original format of the names ( “Last Name, First Name” ) to get the respective names. As there is a comma in the way the best formula to extract this from the cell is to use the SPLIT formula.

By using the split formula, you get the following result for each of the rows below:

	A	B	C
1	Smith, John	Smith =SPLIT(A1,",")	John
2	Doe, Jane	Doe =SPLIT(A2,",")	Jane

Notice also that I’m splitting by a comma AND NOT the comma AND the space , the reason for this is that if you split by a comma AND space you will get the following result if you come across surnames that contain a space:

	A	B	C	D
1	Da Silva, John	Da =SPLIT(A1,", ")	Silva	John
2	Da Silva, Jane	Da Silva =SPLIT(A2,",")	Jane

As you can see if we split by ", " instead of "," we get those surnames containing a space split over each space in their surname. We don’t want this and therefore it’s critical we ONLY split on the comma.

This means the second parameter in a SPLIT function splits on every character it can find in the string and does not split a string based on it meeting the second parameter as if it were a pattern.

INDEX

Next, we want to be able to capture the split result and return the right order of each name. The split result on the last and first name produces a range in that very order: the first range is the last name, the second range is the first name.

By using the INDEX formula you can just capture one range at a time, as seen here:

	A	B	C
1	Smith, John	Smith =INDEX(SPLIT(A1,","), 1)	John =INDEX(SPLIT(A1, ","), 2)
2	Doe, Jane	Doe =INDEX(SPLIT(A2,","), 1)	Jane =INDEX(SPLIT(A2, ","), 2)

CONCATENATE

The final step is then placing the INDEX references into their right order and stitching it all together using the CONCATENATE formula which appends all elements inside into one neat and tidy string.

Therefore, the order of what is to be concatenated would be:

=CONCATENATE(INDEX(SPLIT(A1, ","), 2), " ", INDEX(SPLIT(A1, ","), 1))

Notice how I’m placing the second index item first , followed by a space, then appending the first index item second .

This is our final formula.

Need To TRIM ?

You might not need to apply the last wrapping function TRIM if there is no trailing space after the comma, but it’s probably good practice too just in case in the data entry somebody accidentally inserted some trailing spaces at the end of the names entered.

Therefore, to finish and have a clean data set of our reordered names we apply the TRIM function to make our data clean:

=CONCATENATE(TRIM(INDEX(SPLIT(A1, ","), 2)), " ", TRIM(INDEX(SPLIT(A1, ","), 1)))

Switch First Name & Last Name To Last Name, First Name

Now that you’ve seen how to go one way from a format with the last name followed by a comma, and then the first name, how could you go the other way from first name and last name to the last name, followed by a comma, and then the first name.

For example John Smith to Smith, John .

This would be the same formula as listed above, but with only one small modification. First here’s the formula:

=CONCATENATE(TRIM(INDEX(SPLIT(A1, " "), 2)), ", ", TRIM(INDEX(SPLIT(A1, " "), 1)))

Did you spot the changes?

The second parameter for the SPLIT formulas are now the space character (before it was a comma and space), and the second parameter for the CONCATENATE formula is the comma and space character (before it was just a space character).

	A	B
1	John Smith	Smith, John =CONCATENATE(TRIM(INDEX(SPLIT(A1, " "), 2)), ", ", TRIM(INDEX(SPLIT(A1, " "), 1)))
2	Jane Doe	Doe, Jane =CONCATENATE(TRIM(INDEX(SPLIT(A2, " "), 2)), ", ", TRIM(INDEX(SPLIT(A2, " "), 1)))

Summary

In this article, we explored how to flip the format where names are written in a certain style containing a comma to an appropriate format you can use.

This article explored how to flip from one format where the last name is first, followed by a comma as a separator, and then places the first name last to the format of the first name, then a space, then and last name.

I also showed how you can go from a simple first name, then a space, then a last name to the last name, comma, and first name format.

All of these were done with one fairly easy to understand formula using a variety of other handy formulas within.