Convert LAST NAME, FIRST NAME into different formats

In many databases names may show up as last name, first name which doesn’t make it ideal for sorting and filtering data easily. However this can be quickly rearranged in Excel. I’ll show you two ways to do this: 1) using text to columns (the easier method) and 2) using formulas

1) Using Text-to-Columns

Select the cells that have the last name, first name format and under the Data tab click on Text to Columns.

On the popup that comes up select Delimited and click Next

On the next screen, make sure Comma is the only delimiter selected and then press Finish

Doing this will put the last names in the current range and the first names in the adjacent column B. You will want to make sure the adjacent column is blank to make sure you don’t accidentally overwrite data that may be in those cells already.

An optional step, if you wanted to combine both last name and first name into one cell is to use the following formula in cell C18:

=B18&” “&A18

B18 is the first name and A18 is the last name fields. The ampersand connects the words with a blank space inbetween. Cell C18 would show John Smith

For Excel purposes, it’s probably ideal to have the last name and first name in separate cells rather than in one.

2) Using Formulas

Extracting the Last Name

First, the formula to extract the last name (assuming the cell is A2):

=LEFT(A2,FIND(“,”,A2,1)-1)

If I have Smith, John in cell A2, then the result is as follows:

The LEFT function extracts x number of letters from the left of the cell. The key is the second argument. It consists of:

FIND(“,”,A2,1)-1

The FIND function looks for a comma within cell A2, and begins at the 1st character. If the cell is Smith, John then the comma would be found in the 6th character of that cell. The -1 that is after the FIND function is to reduce this to the 5th character. This is done because I don’t want to include the comma in the name.

So how the LEFT function works is from cell A2 it pulls the first 5 characters from the left, which will result in Smith

Had I not included the -1 after the FIND function, it would have been Smith,


Extracting the First Name

Now I’ll show you how to extract the first name using a similar function, the RIGHT function:

=RIGHT(A2,LEN(A2)-FIND(“,”,A2,1)-1)

The logic behind the RIGHT function is the same as the LEFT function, except this time the formula pulls the characters from the right instead of the left. However you’ll notice the second argument is a little more complex this time. The argument is as follows:

LEN(A2)-FIND(“,”,A2,1)-1

The key difference is I am now using the LEN function which tells me the number of characters that are in the cell. LEN(A2) would return 11 characters for Smith, John

Why can’t I just use the RIGHT function the way I used the LEFT one? The reason being is the FIND function searches from left to right and not from right to left, so it will tell me the position of the comma from the left, not from the right. So if it returns the number 6, I know the comma is the 6th character from the left, but doesn’t tell me how many from the right.

In order to do this I have to use the LEN function. If the total cell is 11 characters long, and the comma is at the 6th character, that will mean there are 5 characters after the comma. Coincidentally in this case the comma is 6 spots from both the left and right, making it right in the middle of the cell.

Here again I use the -1 after the FIND function because if I pull the 5 characters from the right, it will include a blank space (obviously if the data is formatted as last name,first name instead of last name, first name you can omit the -1).

Combining the Two

Now these two functions have allowed me to pull the different parts of the name out of a comma separated last name, first name format. If you want all of this into one formula (and assuming you want first name last name to be in a single cell) then all you would need to do is enter the formula as follows:

=RIGHT(A2,LEN(A2)-FIND(“,”,A2,1)-1)&” “&LEFT(A2,FIND(“,”,A2,1)-1)

I’m reusing the formulas used earlier and connecting them with ampersands while also adding a space inbetween.