As the formula is copied down, it returns the last name from each name in column B.
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.
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:
The main problem is to figure out how many characters to extract, which we do with the 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:
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...
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.