Extract Last Word In Excel Made Easy

Here is the extracted article based on the provided instructions:

Extracting the last word in Excel can be a useful skill to have, especially when working with large datasets. In this article, we will explore the different methods to extract the last word in Excel, including using formulas and VBA macros.

Method 1: Using the RIGHT and LEN Functions

One way to extract the last word in Excel is by using the RIGHT and LEN functions. The LEN function returns the length of a text string, while the RIGHT function returns a specified number of characters from the right side of a text string.

To use this method, follow these steps:

• Select the cell where you want to extract the last word.
• Type the formula: =RIGHT(A1,LEN(A1)-FIND(" ",A1,LEN(A1)-1))
• Press Enter.

This formula works by finding the last space in the text string and then extracting the characters to the right of that space.

Example:

Suppose you have the following text string in cell A1: "Hello World Excel"

Using the formula above, the result would be: "Excel"

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

In Excel 2019 and later, you can use the FILTERXML function to extract the last word from a text string.

To use this method, follow these steps:

• Select the cell where you want to extract the last word.
• Type the formula: =FILTERXML("<t><d>"&SUBSTITUTE(A1," ","</d><d>")&"</d></t>","//d[last()]")
• Press Enter.

This formula works by converting the text string into an XML string, and then using the FILTERXML function to extract the last element (which is the last word).

Example:

Suppose you have the following text string in cell A1: "Hello World Excel"

Using the formula above, the result would be: "Excel"

Method 3: Using VBA Macro

If you prefer to use VBA macros, you can create a custom function to extract the last word from a text string.

To use this method, follow these steps:

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

Function LastWord(text As String) As String
    LastWord = Split(text, " ")(UBound(Split(text, " ")))
End Function

• Save the module by clicking File > Save.

To use the function, follow these steps:

• Select the cell where you want to extract the last word.
• Type the formula: =LastWord(A1)
• Press Enter.

This function works by splitting the text string into an array of words, and then returning the last element of the array.

Example:

Suppose you have the following text string in cell A1: "Hello World Excel"

Using the formula above, the result would be: "Excel"

Excel Image Gallery

We hope this article has helped you learn how to extract the last word in Excel using different methods. Whether you prefer to use formulas, VBA macros, or other techniques, there's a solution that suits your needs. Do you have any questions or need further assistance? Leave a comment below or share this article with your colleagues!