Exceljet

Quick, clean, and to the point

Get first name from name with comma

Excel formula: Get first name from name with comma
Generic formula 
=RIGHT(name,LEN(name)-FIND(",",name)-1)
Summary 

To extract the first name from a full name in "Last, First" format, you can use a formula based on the RIGHT, LEN, and FIND functions. In the example shown, the formula in C5, is:

=RIGHT(B5,LEN(B5)-FIND(",",B5)-1)

As the formula is copied down, it returns the first name from each name in column B.

Explanation 

In this example, the goal is to extract the first name from a list of names in "Last, First" format as seen in column B. There are several ways to approach this problem, but in this example we are going to extract the last name from the right side of the full name with the RIGHT function.

RIGHT function

The RIGHT function takes two arguments, the text itself and num_chars, which specifies how many characters to extract:

=RIGHT(text,num_chars)

For example, if we use "apple" for text and 3 for num_chars, we get "ple":

=RIGHT("apple",3) returns "ple"

So, at a high level, this formula uses RIGHT to extract characters from the right side of the name. The complete formula in C5 is:

=RIGHT(B5,LEN(B5)-FIND(",",B5)-1)

The complexity in the formula comes from working out how many characters to extract.

Length of first name

The main problem is to calculate how many characters to extract or, in other words, the length of the first name. To work this out, we locate the position of the comma (",") in the text, then subtract this number from the total length of the text:

LEN(B5)-FIND(",",B5)-1

The LEN function calculates the total characters in the text:

LEN(B5) // returns 10

Because there are ten characters in "Chang, Amy", LEN returns 10. Next, the FIND function is used to locate the comma (",") in the text:

=FIND(",",B5) // returns 6

Because the comma (",") occurs as the sixth character in the text, FIND returns 6. When we subtract 6 from 10, we get 4:

=LEN(B5)-FIND(",",B5)
=10-6
=4

This is close to what we need, but "Amy" contains 3 characters, not 4 characters. If we ask RIGHT for the last 4 characters in "Chang, Amy", we'll also get the space that follows the comma. So, we need to subtract 1 to take the comma into account:

=LEN(B5)-FIND(",",B5)-1
=10-6-1
=3

The code above returns this result directly to the RIGHT function as the num_chars argument:

RIGHT("Chang, Amy",3) // returns "Amy" 

And RIGHT returns "Amy" as the final result.

No space after comma

The formula above assumes first and last names are separated by a comma and space (", "). If there is no space after the comma, adjust the formula like this:

=RIGHT(B5,LEN(B5)-FIND(",",B5))

This version does not subtract 1, since there is no space character to account for.

Get last name

To extract the last name from the names in column B, you can use a similar formula:

=LEFT(B5,FIND(",",B5)-1)

Note that in this case, we extract text starting from the left with the LEFT function, and the calculation to determine the length of the last name is not as complex. See example here for a full explanation.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.