How to Find a Middle Name in Excel: Formula and Steps

With full names in A2 and names written in first-middle-last order, enter =IFERROR(INDEX(TEXTSPLIT(TRIM(A2)," "),1,2),"") in B2 to return the second word; for example, Jordan Marie Lee returns Marie. This formula uses TEXTSPLIT, available in Microsoft 365 and Excel 2024. If you use an older Excel version, a compatible formula is included below.

What You’ll Need

An Excel workbook with each full name in one cell, such as names in A2:A100.
A blank column beside the names for the extracted middle names.
Microsoft 365 or Excel 2024 for the TEXTSPLIT formula. The alternative formula in this guide works in older Excel versions too.
Names in a consistent order, ideally first name, middle name, then last name, with spaces between the parts.

Excel can extract a word by its position, but it cannot know from a name alone which words are legally or culturally a middle name. Before filling a column, confirm that your source data uses a predictable order and decide how you want to handle people with no middle name, multiple middle names, or a compound first or last name.

Step-by-Step

Check how the names are written. Look at several rows in the source column. The basic method assumes that the first word is the first name and the second word is the middle name. For example, Jordan Marie Lee follows that pattern. If your data uses last-name-first order, includes titles, or mixes formats, the second word may not be a middle name. Note any exceptions before applying a formula to the whole list.
Add a column for the result. If the full names are in column A, type a heading such as Middle Name in B1. Keeping the output separate preserves the original names and makes it easier to check the results. Use a new, empty column so the formula does not overwrite other information. If the list is an Excel table, add the heading in the next available table column.
Enter the modern formula in the first result row. With full names in A2:A100 and the result heading in B1, enter this in B2. The example assumes each middle name is the second space-separated word:

=IFERROR(INDEX(TEXTSPLIT(TRIM(A2)," "),1,2),"")

For Jordan Marie Lee in A2, B2 returns Marie. TRIM removes leading and trailing spaces and reduces repeated ordinary spaces between words to one. TEXTSPLIT separates the cleaned text at spaces, and INDEX returns the first row’s second item. IFERROR displays a blank if there is no second word, such as when A2 contains only a first and last name. The formula does not change the original cell.
Confirm the first few results. Press Enter, then compare B2 with the original name in A2. Check several more entries, including a person with no middle name and any unusual name formats. For example, a name with two given names may produce the second given name rather than a middle name. Fixing those exceptions now is easier than correcting a long filled column later.
Fill the formula down the list. Select B2 and point to the small square at the lower-right corner of the cell, called the fill handle. Drag it down to the last row, or double-click the fill handle if the adjacent name column has continuous data. Excel adjusts A2 to A3, A4, and so on. If you prefer, copy B2, select the remaining result cells, and paste. Check the last few rows to make sure the formula reached the end of the data.
Use a formula for older Excel versions if needed. If Excel reports that TEXTSPLIT is not recognized, use the following alternative. With full names in A2:A100 and the result going in B2, enter:

=IFERROR(MID(TRIM(A2),FIND(" ",TRIM(A2))+1,FIND(" ",TRIM(A2),FIND(" ",TRIM(A2))+1)-FIND(" ",TRIM(A2))-1),"")

For Jordan Marie Lee in A2, this also returns Marie. It finds the first and second spaces and returns the text between them; IFERROR returns a blank when the expected second space is missing. This formula relies on the same first-middle-last order and is less easy to read or adapt than TEXTSPLIT, but uses long-standing Excel functions.
Review and, if appropriate, save fixed values. When the results look right, decide whether the formulas should remain linked to the source names. Keeping formulas is useful if names may change, because the middle-name results update with them. If you need a static copy, copy the result cells, then use Home > Paste > Paste Values. This replaces formulas with their current displayed results. Keep the original full-name column as a reference.

The steps and formulas work in Microsoft 365 for Windows and Mac, and in Excel for the web when TEXTSPLIT is available in your account. Ribbon placement can look a little different on Mac or in the web app. The formula syntax is the same. If a feature or function is unavailable in your Excel version, use the older-version formula above.

Common Mistakes

Assuming the second word is always a middle name. The formula identifies position, not meaning. In Ana Maria Garcia, Maria may be a second given name; in Lee Jordan Kim, Jordan may not be a middle name at all. Confirm the source convention before treating the result as an authoritative middle name.

Overlooking compound names. If a person has two first-name words, the formula returns the second first-name word. If the last name has multiple words, the result may still be right when the middle name is second, but a name with no middle name may be mistaken for having one. The formula cannot determine boundaries that are not marked in the data.

Expecting the formula to return every middle name. For Jordan Marie Claire Lee, the basic formula returns Marie only. It extracts the second word, not all words between the first and last names. Decide whether your task needs a single middle name, a middle initial, or a variable-length set of given names before choosing an approach.

Using the wrong cell reference or overwriting data. If names start in A3 rather than A2, the formula must refer to A3 in the first output row. Put the formula in a blank column and check that it points to the name on the same row. When copying down, Excel normally updates relative references automatically.

Confusing blanks with missing data. A blank result can mean the source has fewer than two words, or it can indicate an unexpected format. It does not prove that the person has no middle name. Review blank results against the original records rather than assuming the formula resolved the name’s meaning.

Tips

Keep a copy of the original data. The formula approach is non-destructive, but retaining the source column makes verification straightforward. If you later paste values, preserve the full names in another column or on a separate sheet. This is especially important when names are used for records that need to match an external source.

Use a table for a list that will grow. Select the data and choose Insert > Table, making sure the option for headers matches your selection. A formula entered in a table column generally fills through that column and can carry into new rows. Verify the formula behavior after adding rows, especially if your workbook has manually added data outside the table.

Check spaces that TRIM does not remove. TRIM handles ordinary spaces but may not remove nonbreaking spaces copied from websites or other systems. If names still appear to have odd spacing, inspect the source and clean the data first. Avoid replacing every punctuation mark or space without reviewing the results; hyphens and apostrophes can be meaningful parts of names.

Test representative edge cases. Before relying on the output, include a typical three-part name, a two-part name, a middle initial, a multiword name, and a row with extra spaces in your review. A sample of varied records can reveal incorrect assumptions that a long run of ordinary names would not expose.

Use a structured workflow for important lists. Keep the raw name, extracted value, and any manual correction in separate columns. If an unusual record needs a correction, record it clearly instead of silently changing the source. That makes it easier for another person to distinguish a formula result from a reviewed exception.

Troubleshooting

You see #NAME? or a function-name error. Your Excel version may not include TEXTSPLIT. It is available in Microsoft 365 and Excel 2024; switch to the older-version formula above if it is not available. Also check that the function is spelled TEXTSPLIT and that the formula begins with an equals sign.

The result is blank when you expected a name. Check the original cell for extra characters, nonbreaking spaces, or a format other than first-middle-last. The formulas intentionally return a blank when the expected second word or second space is missing. You can temporarily inspect the source text or type a test name in an empty cell to see whether the extraction method works on a simple example.

The result is the wrong word. Compare the source order with the formula’s assumption. The modern formula always returns the second space-separated item. If the name includes a title such as Dr., a suffix, or a two-word first name, the second item may not be the middle name. There is no universal formula that can infer those cases reliably from unstructured text; correct the source format or handle exceptions separately.

The formula appears as text instead of calculating. Confirm that the cell does not have Text number formatting, and make sure there is no apostrophe before the equals sign. Change the cell format to General, then re-enter the formula. If Excel is displaying formulas throughout the worksheet, turn off Show Formulas from the Formulas tab and check the result again.

Excel shows a formula error after pasting. Check that the formula was copied intact and that the quotation marks are straight double quotes. The formulas here use commas between arguments, which is the standard syntax for the stated setup. Some regional Excel settings use semicolons as separators; if Excel flags the commas, use the separator your installation expects while keeping the rest of the formula unchanged.

Frequently Asked Questions

Can Excel identify a middle name automatically?
Excel can extract a word based on its position, but it cannot reliably tell whether that word is a middle name. The formula works when your data follows a known order, such as first name, middle name, last name. Records with titles, compound names, or inconsistent order need review.

What if a person has no middle name?
The formulas shown return a blank when they cannot find a second word in the expected pattern. If a two-word name has a first and last name, that alone is not enough to establish that the person has a middle name. Treat blanks as items to verify if completeness matters.

How do I return two middle names?
The TEXTSPLIT formula returns only the second word. It does not know where multiple given names end and a compound surname begins. If your data has a fixed format with exactly one first name and one last name, a different formula can return the words between them, but that assumption should be checked for every record type before use.

Will the formula keep working if I edit a full name?
Yes. While the output remains a formula, changing the referenced name cell recalculates the result. If you use Paste Values, the result becomes static and will not update when the original name changes.

Can I use this in Excel for Mac or Excel for the web?
Yes, the formulas work in Microsoft 365 for Mac and Excel for the web when the required function is available. The formula syntax is the same, though menus and ribbon layout may differ. For older Excel installations that do not support TEXTSPLIT, use the alternative formula.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.