# How to extract the middle name from a full name in Excel

Take the text between the first and the last space: =TEXTBEFORE(TEXTAFTER(A2," ")," ",-1,,,""). Names without a middle name return blank.

**Excel:** `=TEXTBEFORE(TEXTAFTER(A2," ")," ",-1,,,"")` (Microsoft 365 only)

**Google Sheets:** `=IFERROR(REGEXEXTRACT(A2,"^\S+ (.+) \S+$"),"")`

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Full name | Middle name |
| Mary Ann Smith |  |
| Maria Lopez |  |
| John Paul Jones |  |
| Aiko Tanaka |  |
| Robert J. Kim |  |
| Sarah Jane Parker |  |
| Lars Eriksen |  |

Formula in B2, filled to B8: `=TEXTBEFORE(TEXTAFTER(A2," ")," ",-1,,,"")`

Result: Ann; ; Paul; ; J.; Jane; 

## How it works

TEXTAFTER drops the first name, leaving "Ann Smith". TEXTBEFORE with `-1` then keeps everything before the last space, which drops the last name. When there is no middle name, the inner result has no space left, so TEXTBEFORE uses its sixth argument and returns "" instead of #N/A.

## Other ways

- `=IFERROR(MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1),"")`: Works in every version: MID takes the characters between the first and second space. With two middle names it returns only the first one.

## Common mistakes

- Extra spaces shift every position. Wrap the name in TRIM first.
- Suffixes like "Jr." are treated as the last name, so "John Paul Jones Jr." returns "Paul Jones".
- Without the sixth argument, TEXTBEFORE returns #N/A for every two-part name.

## Questions

**How do I get only the middle initial?**

Take the first letter of the result: `=LEFT(TEXTBEFORE(TEXTAFTER(A2," ")," ",-1,,,""),1)`.

**How do I remove the middle name instead?**

Join the first and last names: `=TEXTBEFORE(A2," ")&" "&TEXTAFTER(A2," ",-1)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/extract-middle-name