How to Find a Middle Name in Excel from Full Names

To extract the second word as a middle name, put the full name in A2 and enter this formula in B2: =IF(TRIM(A2)="","",LET(parts,TEXTSPLIT(TRIM(A2)," "),IF(COLUMNS(parts)<3,"",INDEX(parts,1,2)))). It returns a blank when the cell is empty or has fewer than three words. This approach is for Microsoft Excel for Microsoft 365 on Windows; TEXTSPLIT is also available in Excel 2024. Before using it, check that your names follow a consistent order, such as first, middle, last.

What You’ll Need

An Excel workbook containing full names, ideally one name per cell in a single column.
A blank column for the extracted middle names. For example, if full names are in column A, use column B.
Excel for Microsoft 365 or Excel 2024 for the TEXTSPLIT formula. If you have an older Excel version, use the compatible formula in Step 4.

Step-by-Step

Check how the names are written. Review several entries before writing a formula. The main formula below assumes that each name is arranged as first name, middle name, last name, with ordinary spaces between the parts. For instance, it treats “Avery Jordan Lee” as first name Avery, middle name Jordan, and last name Lee. A comma-separated entry such as “Lee, Avery Jordan” has a different structure and needs a different approach. Also decide whether you want just the first middle name or every name between the first and last; formulas for both options are provided below.
Set up a results column. If your full names are in A2:A100, enter a heading such as Middle Name in B1. Keep the original names in column A so you can compare the result with the source. Click B2, the first row containing a name, rather than placing the formula in the heading row. If your data is in an Excel table, add the formula in the first data cell of a new column; Excel will usually copy it down the table column automatically.
Extract the second word with TEXTSPLIT. With full names in A2:A100 and the first result cell B2, enter the formula below. It removes leading, trailing, and repeated ordinary spaces before splitting the name. It then returns the second word when there are at least three words; otherwise, it returns an empty result. For “Avery Jordan Lee,” B2 displays Jordan. For “Avery Lee,” it displays a blank rather than incorrectly treating Lee as the middle name.
=IF(TRIM(A2)="","",LET(parts,TEXTSPLIT(TRIM(A2)," "),IF(COLUMNS(parts)<3,"",INDEX(parts,1,2))))
Press Enter. If Excel shows a formula error, confirm that the formula was entered with straight quotation marks and commas, and check the troubleshooting section below. Excel uses commas in the examples here; depending on regional settings, your installation may require semicolons as argument separators.
Choose whether you need all middle names. The previous formula returns only the second word. For “Avery Jordan Quinn Lee,” that is Jordan, not “Jordan Quinn.” If you want every word between the first and last name, use this alternative in B2, assuming names are in A2:A100. The formula leaves out the first and last words and joins the remaining words with a single space. In the example, the result is Jordan Quinn.
=IF(TRIM(A2)="","",LET(parts,TEXTSPLIT(TRIM(A2)," "),IF(COLUMNS(parts)<3,"",TEXTJOIN(" ",TRUE,DROP(DROP(parts,,1),,-1)))))
This version uses DROP as well as TEXTSPLIT, so it requires Microsoft 365 or Excel 2024. Use the first formula if you only want one middle name or your version does not support these functions.
Use a formula for older Excel versions if necessary. If TEXTSPLIT is not recognized, and full names are in A2:A100 with the result going in B2, enter the following formula. It extracts the second word from a name with at least three words and returns blank if it cannot find the required spaces. TRIM handles repeated ordinary spaces before the positions are calculated.
=IFERROR(TRIM(MID(TRIM(A2),FIND(" ",TRIM(A2))+1,FIND(" ",TRIM(A2),FIND(" ",TRIM(A2))+1)-FIND(" ",TRIM(A2))-1)),"")
This older-version formula returns only the first middle name, not all middle names. It does not require TEXTSPLIT or LET and can be used in many earlier Excel releases.
Copy the formula down the list. Select B2 and drag the small square at the lower-right corner of the cell (the fill handle) down to the last name. You can also select the range B2:B100 and choose Home > Fill > Down after entering the formula in B2. The relative reference A2 changes to A3, A4, and so on as the formula fills down. Check a few results near the bottom as well as the top.
Review and, if needed, save fixed values. Compare the results against the original names, especially entries with two words, several middle names, initials, or suffixes. Formula results update when the source name changes. If you need a static list for another system, copy the result cells and use Home > Paste > Paste Values. Keep an unchanged copy of the original names before replacing or deleting anything.

The formulas work in Excel for Microsoft 365 on Windows, and the formula logic is the same in Excel for Mac and Excel for the web when the required functions are available. Ribbon labels or their placement can vary slightly across platforms. If TEXTSPLIT or DROP is unavailable in your Mac or web version, use the older formula where supported or open the workbook in a version that includes the newer function.

Common Mistakes

Assuming every name has exactly three parts. Real lists include single names, first-and-last names, two middle names, initials, and suffixes. The basic formula deliberately returns only the second word, so it cannot determine a person’s preferred middle name from context. Inspect unusual records rather than treating every output as authoritative.

Confusing the middle initial with the middle name. For “Avery J. Lee,” the second word is J. The formula returns that initial as written; it cannot expand it into a full name. If you need full middle names, they must be present in the source data or obtained from a reliable record.

Using a formula that does not match the name order. A formula based on the second word will not return the intended value from “Lee, Avery Jordan.” In that format, the second word is Avery. Standardize the source format first, or create a formula designed for the comma-separated pattern rather than assuming the order.

Overlooking suffixes and particles. In “Avery Jordan Lee Jr,” the second word is Jordan, so the basic formula works for that example. However, names such as “Avery de Lee” or “Avery Jordan van Lee” may not fit a simple first-middle-last interpretation. Particles can belong to a family name, and a word-based formula cannot identify that meaning reliably.

Using the all-middle-names formula without checking function support. DROP and TEXTSPLIT are newer functions. If Excel reports #NAME?, your version may not support one of them, or a function name may have been mistyped. Use the older formula for a single middle word, or update to Microsoft 365 or Excel 2024 for the newer formula.

Tips

Test the formula on a small copy of your data first. Include a regular three-part name, a two-part name, a name with two middle words, and a blank cell. This quickly shows whether the formula fits the real list.
Keep the source column unchanged while checking results. This makes it easier to spot a name that was entered in an unexpected order or contains an extra title such as Dr.
Use the all-middle-names formula only when the first and last words really are the outer name parts. It treats every word between them as a middle name; it cannot tell whether a particle or title has a different role.
TRIM removes extra ordinary spaces at the start and end and reduces repeated ordinary spaces between words. It does not remove every invisible character. Data copied from websites may contain nonbreaking spaces, which can make names look normal while splitting incorrectly.
For a one-time extraction, you can use a formula and then paste values into a separate column. For a list that changes regularly, leave the formulas in place so results recalculate when names are edited.
Do not delete the original name column until you have checked the extracted values and saved a backup. A formula can split text, but it cannot confirm that the source spelling or name structure is correct.

Troubleshooting

The formula displays #NAME? Check the function names and your Excel version. TEXTSPLIT is available in Microsoft 365 and Excel 2024; LET is also required by the examples above. The all-middle-names formula additionally requires DROP. If you use an older release, try the MID and FIND formula in Step 5. If Excel rejects commas as separators, replace the argument separators with the separator required by your regional settings.

The formula returns a blank for a name you expected it to extract. The formulas return blank for an empty cell or a name with fewer than three words. Check for a missing middle name, an unexpected delimiter such as a comma, or an entry where the last name has multiple words. If a name has only first and middle names, the formula cannot know that its second word is a middle name rather than a last name.

The result contains the wrong word. Count the words in the source cell and verify the order. The single-name formula always returns word two. The all-middle-names formula returns everything between the first and final words. Neither formula interprets titles, suffixes, or surname particles. Correct the source or handle that record separately if its structure differs.

Names look spaced correctly but do not split. The source may contain nonbreaking spaces or other hidden characters. One practical fix is to clean a copy of the source using Find and Replace: copy the column, select the copy, press Ctrl+H on Windows (Control+H on Mac), and replace the unusual space character with an ordinary space. You may need to copy that character from a problem cell into the Find what box. Then rerun the extraction and check the results.

The output shows the formula instead of a result. The cell may be formatted as Text, or Excel may be treating the entry as text. Change the cell format to General, edit the cell, and press Enter again. Also check that the formula begins with an equals sign and that Show Formulas is not switched on; the Windows shortcut for toggling formula display is Ctrl+grave accent.

Frequently Asked Questions

Can Excel extract a middle name when a person has two middle names?
Yes. Use the all-middle-names formula in Step 4 when the name is structured as first name, middle name or names, then last name. It returns all the words between the first and last. Review names with particles or suffixes because the formula cannot identify their meaning.

What if a person has no middle name?
The formulas above return a blank for a name with fewer than three words. That is a useful default when the data follows first-name-last-name order, but confirm the record if names may have a different format.

Can I extract only a middle initial?
The first formula returns the second word exactly as entered. If the source contains “Avery J. Lee,” the result is J. It will not shorten a full middle name to an initial; that requires a separate formula or editing step.

Will the formula work with names separated by commas?
Not reliably. The examples expect spaces between name parts and a first-middle-last order. A value such as “Lee, Avery Jordan” needs a formula that accounts for the comma and the reversed order, or the source should be standardized before extraction.

Does this work in Excel for Mac or Excel for the web?
The formulas work there when the version supports the functions used. TEXTSPLIT is available in Microsoft 365 and Excel 2024, and the all-middle-names option also needs DROP. If either function is missing, use the older MID and FIND formula where available.

Can I turn the formula results into plain text?
Yes. Copy the result cells, then choose Home > Paste > Paste Values. The cells will no longer recalculate when the source names change, so check the results before replacing the formulas.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.