Exceljet

Quick, clean, and to the point

Get last name from name with comma

Excel formula: Get last name from name with comma
Generic formula 
=LEFT(name,FIND(",",name)-1)
Summary 

To get the last name from a full name in "Last, First" format, you can use a formula that combines the LEFT function and the FIND function. In the example shown, the formula in D5, is:

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

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

Explanation 

In this example, the goal is to extract the last name from a list of names in "Last, First" format as seen in column B. The easiest way to solve this problem is to extract text from the left side of the text string with the LEFT function.

LEFT function

The LEFT function extracts text starting at the left side of a text string. LEFT takes two arguments, text and num_chars, which indicates how many characters to extract:

=LEFT(text,num_chars)

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

=LEFT("apple",3) returns "app"

So, at a high level, this formula uses LEFT to extract characters from the left side of the full name. The complete formula in D5 is:

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

The main problem is to figure out how many characters to extract, which we do with the FIND function.

FIND function

The main challenge in this example is to calculate how many characters to extract, which is equal to the length of the last name. To work this out, we can use the location of the comma (",") in the text. To get the position of the comma, we use the FIND function like this:

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

FIND returns the location of text as a numeric position. Because the comma appears as the 6th character in the text, the FIND function returns 6. This is one more character than we need, so we subtract 1:

FIND(",",B5)-1 // returns 5

The result is 5, which is the length of the last name in "Chang, Amy". The code above returns this result directly to the LEFT function as the num_chars argument:

LEFT("Chang, Amy",5) // returns "Chang" 

And LEFT returns "Chang" as the final result.

Get first name

To extract the first name from names in column B, you can use a formula based on similar ideas:

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

Note that in this case, we extract text starting from the right with the RIGHT function, and the calculation to determine the length of the first name is a little more 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.