As the formula is copied down, it returns the last name from each name in column B.
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: For example, if we use “apple” for text and 3 for num_chars, we get “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: 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 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: 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: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
