To extract the first name from a full name in "Last, First" format, you can use a formula that uses RIGHT, LEN and FIND functions. In the generic form of the formula (above), name is a full name in this format:
A comma and space separate the last name from the first name.
In the example, the active cell contains this formula:
At a high level, this formula uses RIGHT to extract characters from the right side of the name. To figure out the number of characters that need to be extracted to get the first name, the formula uses the FIND function to locate the position of ", " in the name:
At a high level, this formula uses LEFT to extract characters from the left side of the name. To figure out the number of characters that need to be extracted to get the last name, the formula uses the FIND function to locate the position of ", " in...
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...
The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.
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.