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

In Excel 365 use =TEXTAFTER(A2," ",-1), which returns everything after the last space. In older versions use =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)).

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

**Google Sheets:** `=REGEXEXTRACT(A2,"[^ ]+$")`

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Full name | Last name |
| Maria Lopez |  |
| James O'Neil |  |
| Mary Ann Smith |  |
| Lars Eriksen |  |
| Chidi Okafor |  |
| Aiko Tanaka |  |
| Robert J. Kim |  |

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

Result: Lopez; O'Neil; Smith; Eriksen; Okafor; Tanaka; Kim

## How it works

The third argument of TEXTAFTER is the instance number, and a negative number counts from the end. `-1` means the last space, so middle names and initials are skipped and only the final word comes back.

In Google Sheets, `[^ ]+$` matches the run of non-space characters at the end of the text.

## Other ways

- `=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))`: Works in every version. Each space becomes 100 spaces, so the last 100 characters hold only the last word plus padding, which TRIM removes.
- `=INDEX(SPLIT(A2," "),1,COUNTA(SPLIT(A2," ")))`: Google Sheets: split the name into words and take the last one.

## Common mistakes

- A trailing space makes the "last word" empty. Clean the column first: `=TEXTAFTER(TRIM(A2)," ",-1)`.
- Suffixes come back as the last name: "Robert Kim Jr." returns "Jr.". Remove them with SUBSTITUTE before extracting.
- A single-word name returns #N/A. Give TEXTAFTER a fallback: `=TEXTAFTER(A2," ",-1,,,A2)`.

## Questions

**How do I get the last name when the cell says "Lopez, Maria"?**

Take what comes before the comma: `=TEXTBEFORE(A2,",")`.

**How do I get the last word of any sentence?**

The same formula works on any text: `=TEXTAFTER(A2," ",-1)` returns the last word.

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-last-name