As the formula is copied down, it returns the first name from each name in column B.
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.
The RIGHT function takes two arguments, the text itself and num_chars, which specifies how many characters to extract:
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:
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:
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...
The FIND function finds the first space character (" ") in the name and returns the position of that space in the full name. The number 1 is subtracted from this number to account for the space itself. This number is used by the LEFT function as the...
At the core, this formula uses the RIGHT function to extract characters starting from the right. The other functions which make up the complex part of this formula just do one thing: they calculate how many characters need to be extracted. At a high...
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.