5 Ways To Get The Last Word In Excel

Microsoft Excel is an incredibly powerful tool for data analysis and manipulation. One of the most common tasks in Excel is working with text strings, and a frequent requirement is extracting the last word from a cell or a range of cells. The last word can be crucial in various applications, such as data cleaning, formatting, and analysis. In this article, we will explore five different methods to get the last word in Excel.

Method 1: Using the TEXTSPLIT Function (Excel 2021 and Later)

One of the most straightforward methods to extract the last word in Excel is by using the TEXTSPLIT function, which is available in Excel 2021 and later versions. The TEXTSPLIT function splits text into an array of substrings based on a specified delimiter. Here's how you can use it to get the last word:

1. Select the cell where you want to display the last word.
2. Enter the formula: =TEXTSPLIT(A1," ") (assuming the text is in cell A1).
3. Press Enter to get the array of words.
4. Since we want the last word, we can use the INDEX and COUNTA functions to achieve this.
5. Enter the formula: =INDEX(TEXTSPLIT(A1," "),COUNTA(TEXTSPLIT(A1," "))).
6. Press Enter to get the last word.

Example

Suppose you have the text "Hello World" in cell A1, and you want to extract the last word "World". Using the TEXTSPLIT function, you can get the last word as follows:

Formula	Result
=TEXTSPLIT(A1," ")	{"Hello", "World"}
=INDEX(TEXTSPLIT(A1," "),COUNTA(TEXTSPLIT(A1," ")))	"World"

Method 2: Using the FILTERXML Function (Excel 2019 and Later)

The FILTERXML function is another powerful function in Excel that can be used to extract the last word from a text string. This function is available in Excel 2019 and later versions. Here's how you can use it:

1. Select the cell where you want to display the last word.
2. Enter the formula: =FILTERXML("<t><d>"&SUBSTITUTE(A1," ","</d><d>")&"</d></t>","//d[last()]") (assuming the text is in cell A1).
3. Press Enter to get the last word.

Example

Suppose you have the text "Hello World" in cell A1, and you want to extract the last word "World". Using the FILTERXML function, you can get the last word as follows:

Formula	Result
=FILTERXML(""&SUBSTITUTE(A1," ","")&"","//d[last()]")	"World"

Method 3: Using the TRIM and RIGHT Functions

The TRIM and RIGHT functions can be combined to extract the last word from a text string. Here's how you can use them:

1. Select the cell where you want to display the last word.
2. Enter the formula: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))) (assuming the text is in cell A1).
3. Press Enter to get the last word.

Example

Suppose you have the text "Hello World" in cell A1, and you want to extract the last word "World". Using the TRIM and RIGHT functions, you can get the last word as follows:

Formula	Result
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))	"World"

Method 4: Using the WORDS Function (Excel 2021 and Later)

The WORDS function is a new function in Excel 2021 and later versions that can be used to extract the individual words from a text string. Here's how you can use it to get the last word:

1. Select the cell where you want to display the last word.
2. Enter the formula: =WORDS(A1) (assuming the text is in cell A1).
3. Press Enter to get the array of words.
4. Since we want the last word, we can use the INDEX and COUNTA functions to achieve this.
5. Enter the formula: =INDEX(WORDS(A1),COUNTA(WORDS(A1))).
6. Press Enter to get the last word.

Example

Suppose you have the text "Hello World" in cell A1, and you want to extract the last word "World". Using the WORDS function, you can get the last word as follows:

Formula	Result
=WORDS(A1)	{"Hello", "World"}
=INDEX(WORDS(A1),COUNTA(WORDS(A1)))	"World"

Method 5: Using VBA Macro

If you are comfortable with VBA programming, you can create a macro to extract the last word from a text string. Here's a simple macro that does this:

1. Open the Visual Basic Editor by pressing Alt + F11 or by navigating to Developer > Visual Basic in the ribbon.
2. In the Visual Basic Editor, click Insert > Module to create a new module.
3. Paste the following code in the module:

Function GetLastWord(text As String) As String
    Dim words As Variant
    words = Split(text, " ")
    GetLastWord = words(UBound(words))
End Function

4. Save the module by clicking File > Save.
5. Close the Visual Basic Editor.
6. Select the cell where you want to display the last word.
7. Enter the formula: =GetLastWord(A1) (assuming the text is in cell A1).
8. Press Enter to get the last word.

Example

Suppose you have the text "Hello World" in cell A1, and you want to extract the last word "World". Using the VBA macro, you can get the last word as follows:

Formula	Result
=GetLastWord(A1)	"World"

Last Word in Excel Image Gallery

In conclusion, there are several methods to get the last word in Excel, including using the TEXTSPLIT function, FILTERXML function, TRIM and RIGHT functions, WORDS function, and VBA macro. Each method has its own strengths and limitations, and the choice of method depends on the specific requirements of the task. By mastering these methods, you can efficiently extract the last word from text strings in Excel. Share your thoughts and experiences with extracting the last word in Excel in the comments below!