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

Join the first word and the last word: =TEXTBEFORE(A2," ")&" "&TEXTAFTER(A2," ",-1). Names with no middle name come back unchanged.

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

**Google Sheets:** `=REGEXREPLACE(A2,"^(\S+) .* (\S+)$","$1 $2")`

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Full name | First and last |
| Mary Ann Smith |  |
| Maria Lopez |  |
| John Paul Jones |  |
| Robert J. Kim |  |
| Sarah Jane Parker |  |
| Aiko Tanaka |  |

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

Result: Mary Smith; Maria Lopez; John Jones; Robert Kim; Sarah Parker; Aiko Tanaka

## How it works

TEXTBEFORE returns the text before the first space (the first name) and TEXTAFTER with `-1` returns the text after the last space (the last name). Whatever sits between them, a middle name, an initial or nothing, is dropped.

The Google Sheets pattern only matches names with something between two spaces, so two-part names pass through untouched.

## Other ways

- `=LEFT(A2,FIND(" ",A2))&TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))`: Works in every version: LEFT keeps the first name and its space, and the TRIM(RIGHT(SUBSTITUTE())) part returns the last word.

## Common mistakes

- A single-word name returns #N/A. Wrap it: `=IFERROR(TEXTBEFORE(A2," ")&" "&TEXTAFTER(A2," ",-1),A2)`.
- Two-word first names like "Mary Ann" lose their second half. If that matters, keep the original column and review those rows.

## Questions

**How do I keep the middle initial instead of dropping it?**

For three-part names: `=TEXTBEFORE(A2," ")&" "&MID(A2,FIND(" ",A2)+1,1)&". "&TEXTAFTER(A2," ",-1)` gives "Mary A. Smith".

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

Use `=TEXTBEFORE(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/remove-middle-name