As the formula is copied down, it returns the first name from each name in column B.
RIGHT function
The RIGHT function takes two arguments, the text itself and num_chars, which specifies how many characters to extract: For example, if we use “apple” for text and 3 for num_chars, we get “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: 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: The LEN function calculates the total characters in the text: Because there are ten characters in “Chang, Amy”, LEN returns 10. Next, the FIND function is used to locate the comma (",") in the text: Because the comma (",") occurs as the sixth character in the text, FIND returns 6. When we subtract 6 from 10, we get 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: The code above returns this result directly to the RIGHT function as the num_chars argument: 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: 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: 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.
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.
