If you need to get the middle name from a full name, and you already have the first and last names in separate cells, you can use a formula that extracts the middle name using the MID and LEN functions, with help from TRIM function.
Note: this is a pretty sloppy formula, but will work in many situations because TRIM cleans up extra spaces, including the case where there is no middle name. It won't work if the names contain titles or suffixes that occur before the first name or after the last name.
Assuming you have a full name in column B, a first name in column C, and a last name in column D, you can use a formula that looks like this:
At the core, the MID function extracts text from the full name starting at 1 character after the length of the first name. The total characters extracted is equal to the length of the full name minus the length of the first and last names put together.
By design, the formula extracts all text between the first name and the last name, including extra space characters, and then relies on the brute force of TRIM to clean everything up in the end:
1. When there is a middle name. MID gets the middle name (with space on either side) and TRIM removes the extra space.
2. When there is more than one middle name, MID gets all middle names (with space on either side) and trim strips the extra space characters.
3. When there is no middle name, it MID returns a space character, which is removed by TRIM, leaving nothing.
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...
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...
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...
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.