Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Last row number in range
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Multi-criteria lookup and transpose in Excel
  • Two-way lookup with VLOOKUP in Excel
  • Left Lookup in Excel

Data Analysis Examples

  • Example of COUNTIFS with variable table column in Excel
  • Use Data Form to input, edit and delete records in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • How to combine 2 or more chart types in a single chart in Excel
  • Get column name from index in Excel Table

Data Validation Examples

  • Excel Data validation allow uppercase only
  • Excel Data validation unique values only
  • Excel Data validation only dates between
  • Excel Data validation no punctuation
  • Excel Data validation must contain specific text

Extract middle name from full name — Manipulating NAMES in Excel

by

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.

Worked Example:   Basic outline numbering in Excel

Formula

=TRIM(MID(name,LEN(first)+1,LEN(name)-LEN(first&last)))

Explanation

How this formula works

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:

=TRIM(MID(B5,LEN(C5)+1,LEN(B5)-LEN(C5&D5)))

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.

Worked Example:   How to Capitalize first letter in a sentence in Excel

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:

Worked Example:   Put names into proper case -- Manipulating NAMES in Excel

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.

Post navigation

Previous Post:

Get last name from name with comma — Manipulating NAMES in Excel

Next Post:

Join first and last name — Manipulating NAMES in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to add a line break with a formula while joining text strings in Excel
  • Find most frequent text within a range with criteria in Excel
  • How to extract last two words from text string in Excel
  • How to strip non-numeric characters in Excel
  • SUBSTITUTE function: Description, Usage, Syntax, Examples and Explanation
  • Calculate date overlap in days in Excel
  • Convert date to text in Excel
  • Roll back weekday to Friday base on a particular date in Excel
  • Get first day of month in Excel
  • Convert text timestamp into time in Excel
  • FVSCHEDULE function: Description, Usage, Syntax, Examples and Explanation
  • Example of Future value of annuity in Excel
  • Bond valuation example in Excel
  • PV function: Description, Usage, Syntax, Examples and Explanation
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles