The formula
Excel & Google SheetsExcelGoogle Sheets
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))In C2 → Smith-Jones
Second exampleExcelGoogle Sheets
=TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",100)),100))In C3 → mug
Excel 365Excel 365
=TEXTAFTER(A2," ",-1)In C4 → Smith-Jones
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 1 result for formulas tagged Excel 365 was checked with a reference implementation of the documented behavior, because LibreOffice can't run that function.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | |
|---|---|---|---|
| 1 | Text | ||
| 2 | Mary Ann Smith-Jones | Smith-Jones | |
| 3 | Blue ceramic mug | mug | |
| 4 | Smith-Jones |
How it works
- SUBSTITUTE(A2," ",REPT(" ",100))
- Every space becomes 100 spaces.
- RIGHT(…,100)
- The last 100 characters: only spaces plus the last word.
- TRIM(…)
- Strips the padding.
- TEXTAFTER(A2," ",-1)
- Text after the last space (instance -1 counts from the end).
Common errors and fixes
Blank result?
The cell ends with a space. Use TRIM(A2) inside SUBSTITUTE.
Word cut off?
A word longer than 100 characters. Increase both 100s.
Does this work in Google Sheets?
The formulas tagged "Google Sheets" work as-is. Formulas tagged only "Excel" may need ARRAYFORMULA or a different function in Sheets.