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. 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.

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.