To return the final word in a full name in Excel, enter =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",LEN(TRIM(A2)))),LEN(TRIM(A2)))) in B2, assuming the full name is in A2. For “John Quincy Adams,” the result is “Adams.” This RIGHT-and-SUBSTITUTE method works in current Excel and older versions too, but it extracts the last word—not necessarily a person’s complete surname.
The RIGHT and SUBSTITUTE formula
The formula is useful when a column contains names in a consistent “given name followed by family name” order and you want the last space-separated part in a separate column. The TRIM functions remove leading or trailing ordinary spaces and reduce repeated spaces between words, helping prevent stray spaces from affecting the result. Copy the formula into the first result row, then fill it down for the remaining records.
For example, if A2 contains “Maria Elena Garcia,” the result is “Garcia.” If A2 contains “David Lee,” it returns “Lee.” It also returns the final part of a single-word entry unchanged. The formula uses functions available in older desktop editions as well as Microsoft 365, so it is useful when a workbook may be opened in different Excel versions.
The formula does not identify a surname by meaning or by personal naming convention. It finds the last word after spaces. That distinction matters for compound family names, suffixes, and names stored in a different order.
How the formula works
The formula combines four functions. RIGHT returns characters from the end of a text string; SUBSTITUTE replaces matching text; REPT repeats text; LEN counts characters; and TRIM removes extra ordinary spaces. Together, these functions create a long space-filled gap before each word, then use RIGHT to capture the final section and TRIM to remove the padding.
- TRIM(A2) tidies ordinary spaces in the input. For example, “ John Quincy Adams ” becomes “John Quincy Adams.”
- LEN(TRIM(A2)) counts the characters in that tidied name. The formula uses this count as the length of a repeated-space block.
- SUBSTITUTE(…,” “,REPT(” “,…)) replaces each normal space between words with a much longer run of spaces.
- RIGHT(…,LEN(…)) takes a section from the end of the expanded text. That section includes the last word and padding before it.
- TRIM(…) removes the padding, leaving the final word.
Using a repeated-space block based on the input’s length is the key trick: it makes the last word long enough to be isolated without needing to know how many words are in the name. The formula does not change the source cell, so the original full name remains available.
Enter the formula and fill it down
Keep the full names in one column and put the formula in an adjacent column. In this example, names are in A2:A100 and the extracted values go in B2:B100. A2 is the first name cell, so the relative reference adjusts to A3, A4, and so on as you copy the formula.
- Type a heading such as “Last word” in B1.
- Select B2 and enter the formula from the opening paragraph.
- Press Enter and check the returned value against the name in A2.
- Copy B2 down the column by dragging its fill handle or copying and pasting into the remaining rows.
- Review entries with compound names, suffixes, or unusual spacing before using the results elsewhere.
If you need a fixed list rather than formulas that change when source data changes, copy the results and use Home > Paste > Paste Values. Excel for Mac has the same formula and a similar Home tab paste-values option; ribbon labels and layout can vary slightly by version. Excel for the web also supports this formula. In some regional settings, Excel expects semicolons instead of commas between function arguments. If Excel reports a formula error as soon as you enter it, check the list separator used by your installation.
Names the formula handles—and names it cannot interpret
The formula works well for many straightforward entries: “Ava Patel” returns “Patel,” “Noah James Carter” returns “Carter,” and “Ana María López” returns “López.” Accented letters, apostrophes, and hyphens are not spaces, so a final token such as “O’Neill” or “Smith-Jones” stays together.
However, a “last word” is not always the whole family name. For “Maria de la Cruz,” the formula returns “Cruz,” not “de la Cruz.” For “Jean Van der Meer,” it returns “Meer.” A hyphenated surname such as “Smith-Jones” remains one token, but a surname written as separate words does not.
It also follows the text exactly rather than recognizing suffixes or name order:
- “Robert Brown Jr.” returns “Jr.”
- “Brown, Robert” returns “Robert,” because the final word is the given name.
- “Cher” returns “Cher,” since there is no space to split.
- “Alex Kim (Contractor)” returns “(Contractor),” not “Kim.”
These are not formula errors. They show why it is important to decide what the source data represents before labeling the output “Last name.” If the list mixes formats or includes annotations, clean or standardize the data first, or use a rule tailored to those formats.
Spaces, blanks, and other data problems
TRIM handles ordinary spaces, including multiple spaces between words and spaces at the beginning or end of a cell. That helps with names pasted from many documents, but not every invisible character is an ordinary space. A nonbreaking space, often introduced when copying from a web page, may look identical while behaving differently. If a result seems to include unexpected spacing, check the source data and consider replacing nonbreaking spaces with ordinary spaces before extracting words.
Blank cells should be checked before treating the output as a real name. Depending on the exact formula and input, an empty entry may produce an empty-looking result; it is still wise to distinguish a genuinely blank record from a person whose name is missing. You can filter the original name column for blanks and review those records separately. Avoid assuming that every row contains a valid full name just because the formula has been filled down.
Other common issues include titles, middle initials, commas, and notes appended to a name. A trailing title such as “Dr.” or a note such as “(new)” becomes the returned last word. If names come from a database export, check its documentation or a sample of records to understand whether commas separate family and given names, and whether suffixes occupy their own field.
A newer alternative: TEXTAFTER
In Microsoft 365 or Excel 2024, TEXTAFTER can express the “text after the last space” operation more directly. With the full name in A2, enter =IFERROR(TEXTAFTER(TRIM(A2)," ",-1),TRIM(A2)). For “John Quincy Adams,” it returns “Adams”; for a single-word entry, IFERROR returns the trimmed original because there is no space to find.
The -1 argument tells TEXTAFTER to search from the end for the last occurrence of the delimiter. This shorter formula can be easier to understand, but it is not suitable if the workbook must work in older Excel editions that do not include TEXTAFTER. The RIGHT-and-SUBSTITUTE formula is the more compatible choice. Both methods return the final word, so neither solves the compound-surname or suffix problem by itself.
Choose based on your workbook’s audience. If everyone uses Microsoft 365 or Excel 2024, TEXTAFTER is concise. If colleagues may open the file in an older version, use RIGHT and SUBSTITUTE or provide the extracted results as values after checking them.
Check results before using them
Before using extracted values for mailing, matching, reporting, or importing into another system, compare a sample with the original names. Pay special attention to entries with two or more spaces, single-word names, suffixes, and multiword surnames. A formula can apply a consistent text rule to every row, but it cannot verify that the rule matches a person’s actual family name.
If the output will be used to match records, avoid relying on last names alone. Many people share a surname, and a surname may be spelled differently across records. Use a stable identifier where available, or compare additional fields under the rules of the system you are working with. Keep an unchanged copy of the source column so corrections can be traced and the original values are not lost.
For a one-time task, formula results can be pasted as values after review. For a workbook that receives new names regularly, keep the formula in the result column and establish a clear data-entry rule—for example, one person per row, names in the same order, and no appended notes. A consistent source format is often more important than choosing between two formulas that both return the last word.
Frequently Asked Questions
What is the RIGHT SUBSTITUTE formula to get the last name in Excel?
With a full name in A2, use =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",LEN(TRIM(A2)))),LEN(TRIM(A2)))). It returns the final space-separated word. For “John Quincy Adams,” the result is “Adams.”
Does this formula work for a middle name?
Yes. It returns the final word regardless of how many earlier words appear. “John Quincy Adams” returns “Adams”; it does not need to know that “Quincy” is a middle name.
Will it return a complete compound last name?
Not if the surname contains separate words. For “Maria de la Cruz,” it returns “Cruz.” You need a consistent naming rule or a manually reviewed list to preserve multiword surnames.
Why does my formula return “Jr.” or a title?
The formula extracts the last word in the cell. If a suffix, title, or note comes last, that is what it returns. Remove or separately handle those items before using the output as a surname.
Can TEXTAFTER replace RIGHT and SUBSTITUTE?
Yes, in Microsoft 365 or Excel 2024, TEXTAFTER can return the text after the last space. It is shorter, but it still extracts the last word and is unavailable in some older Excel versions.
Can I use the formula in Google Sheets?
Google Sheets has many of the same text functions, but its version support and formula behavior are separate from Excel. If you are working in Sheets, use and verify a Sheets-specific formula rather than assuming every Excel feature or function behaves identically.
Related
- Randomize Numbers in Excel Without Duplicates
- Rank in Excel: How to Rank Values Largest to Smallest
- Randomize Names in Excel Without Repeats: Easy Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Random Selection From a List: GuideTo return one random item from a contiguous list in Excel, use =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20))). If your items are in…
- Excel SUBTOTAL: Count Unique Values in Filtered DataExcel’s SUBTOTAL function cannot count unique values by itself. In Microsoft 365, combine UNIQUE and FILTER with SUBTOTAL…
- Calculate Quarterly Dates in Excel: Formulas and ExamplesTo calculate a calendar quarter’s first day in Excel, put a date in A2 and enter =DATE(YEAR(A2),3*INT((MONTH(A2)-1)/3)+1,1); for…
- Sort in Excel and Move Entire Rows Without Splitting DataTo sort in Excel and move entire rows together, select the full data range, then choose Data >…
- Excel IF Formula for Dates: Examples and StepsTo use IF with dates in Excel, test a cell containing a real date and return the label…
- Excel LAMBDA Examples for Reusable Custom FunctionsExcel’s LAMBDA function lets you turn a formula into a reusable custom function. For example, a definition with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.