Table of Contents
Extracting the last word from a cell in Excel refers to the process of retrieving the final word or text string from a given cell in an Excel spreadsheet. This can be achieved by using various built-in functions or formulas such as RIGHT, LEN, and FIND, depending on the specific requirement. The extracted last word can then be used for further analysis or manipulation within the spreadsheet. This method is commonly used in data manipulation and analysis tasks, as it allows for efficient extraction of specific information from a cell without the need for manual sorting or editing.
Excel: Extract Last Word from Cell
You can use the following formula to extract the last word from a cell in Excel:
=TEXTAFTER(A2, " ", -1)
This particular formula extracts the last word from cell A2.
The following example shows how to use this formula in practice.
Example: How to Extract Last Word in Excel
Suppose we have the following column of strings in Excel:
Suppose we would like to extract the last word from each cell in column A.
We can type the following formula into cell B2 to do so:
=TEXTAFTER(A2, " ", -1)
We can then click and drag this formula down to each remaining cell in column B:
Column B now contains the last word from each corresponding cell in column A.
For example:
- The formula extracts manatee from My favorite animal is a manatee
- The formula extracts day from This is a great day
- The formula extracts fun from We should have fun
And so on.
How This Formula Works
This function uses the following syntax:
TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
where:
- text: Text to search
- delimiter: Character or substring to extract text after
- instance_num (optional): Instance of delimiter after which to extract text (default is 1)
- match_mode (optional): 0 = case-sensitive (default), 1 = case-insensitive
- match_end (optional): Treat end of text as delimiter (disabled by default)
- if_not_found (optional): Value to return if delimiter is not found
Recall that we used the following syntax to extract the last word from cell A2:
=TEXTAFTER(A2, " ", -1)
By using a value of -1 for the instance_num argument, we were able to specify that we wanted to extract the text after the last instance of a space.
This is the same as extracting the last word from the cell.
Note: You can find the complete documentation for the TEXTAFTER function in Excel .
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: