5 Ways To Extract Rightmost Word In Excel

Extracting the rightmost word in a text string is a common task in Excel, and there are several ways to accomplish this. In this article, we'll explore five different methods to extract the rightmost word in Excel, using a combination of formulas, functions, and text manipulation techniques.

Method 1: Using the TEXTSPLIT Function

The TEXTSPLIT function is a relatively new addition to Excel, introduced in Excel 2021 and later versions. This function allows you to split a text string into individual words or phrases, making it easy to extract the rightmost word.

To use the TEXTSPLIT function, follow these steps:

1. Select the cell where you want to extract the rightmost word.
2. Type =TEXTSPLIT(A1, " "), where A1 is the cell containing the text string you want to extract the rightmost word from.
3. Press Enter to execute the formula.
4. The TEXTSPLIT function will return an array of words, with the rightmost word as the last element in the array.
5. To extract the rightmost word, use the INDEX and TEXTSPLIT functions together: =INDEX(TEXTSPLIT(A1, " "), 0, TEXTSPLIT(A1, " ").Columns.Count)

This formula will return the rightmost word in the text string.

Example:

Suppose you have the following text string in cell A1: "Hello World, this is an example sentence."

Using the TEXTSPLIT function, you can extract the rightmost word as follows:

=INDEX(TEXTSPLIT(A1, " "), 0, TEXTSPLIT(A1, " ").Columns.Count)

This formula will return the word "sentence".

Method 2: Using the FILTERXML Function

The FILTERXML function is a powerful function in Excel that allows you to filter and extract data from XML strings. While not specifically designed for text manipulation, the FILTERXML function can be used to extract the rightmost word in a text string.

To use the FILTERXML function, follow these steps:

1. Select the cell where you want to extract the rightmost word.
2. Type =FILTERXML("<t><d>" & SUBSTITUTE(A1, " ", "</d><d>") & "</d></t>", "//d[last()]"), where A1 is the cell containing the text string you want to extract the rightmost word from.
3. Press Enter to execute the formula.
4. The FILTERXML function will return the rightmost word in the text string.

This formula works by creating an XML string with each word as a separate element, and then using the //d[last()] XPath expression to extract the last element (i.e., the rightmost word).

Example:

Suppose you have the following text string in cell A1: "Hello World, this is an example sentence."

Using the FILTERXML function, you can extract the rightmost word as follows:

=FILTERXML("<t><d>" & SUBSTITUTE(A1, " ", "</d><d>") & "</d></t>", "//d[last()]")

This formula will return the word "sentence".

Method 3: Using the RIGHT and FIND Functions

This method uses a combination of the RIGHT and FIND functions to extract the rightmost word in a text string.

To use this method, follow these steps:

1. Select the cell where you want to extract the rightmost word.
2. Type =RIGHT(A1, LEN(A1) - FIND("@", SUBSTITUTE(A1, " ", "@", LEN(A1) - LEN(SUBSTITUTE(A1, " ", ""))))), where A1 is the cell containing the text string you want to extract the rightmost word from.
3. Press Enter to execute the formula.
4. The formula will return the rightmost word in the text string.

This formula works by using the FIND function to locate the last space character in the text string, and then using the RIGHT function to extract the word that starts from that position.

Example:

Suppose you have the following text string in cell A1: "Hello World, this is an example sentence."

Using the RIGHT and FIND functions, you can extract the rightmost word as follows:

=RIGHT(A1, LEN(A1) - FIND("@", SUBSTITUTE(A1, " ", "@", LEN(A1) - LEN(SUBSTITUTE(A1, " ", "")))))

This formula will return the word "sentence".

Method 4: Using VBA

If you're comfortable with VBA, you can create a custom function to extract the rightmost word in a text string.

To create a VBA function, follow these steps:

1. Press Alt + F11 to open the VBA Editor.
2. In the VBA Editor, insert a new module by clicking Insert > Module.
3. Paste the following code into the module:

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

4. Save the module by clicking File > Save.
5. Return to your Excel worksheet and select the cell where you want to extract the rightmost word.
6. Type =RightmostWord(A1), where A1 is the cell containing the text string you want to extract the rightmost word from.
7. Press Enter to execute the formula.

This VBA function uses the Split function to split the text string into individual words, and then returns the last word in the array.

Example:

Suppose you have the following text string in cell A1: "Hello World, this is an example sentence."

Using the VBA function, you can extract the rightmost word as follows:

=RightmostWord(A1)

This formula will return the word "sentence".

Method 5: Using Power Query

If you're using Excel 2010 or later, you can use Power Query to extract the rightmost word in a text string.

To use Power Query, follow these steps:

1. Select the cell where you want to extract the rightmost word.
2. Go to the Data tab in the ribbon and click From Text/CSV.
3. In the Power Query Editor, click Split Column > By Delimiter.
4. In the Split Column by Delimiter dialog box, select Space as the delimiter and click OK.
5. In the Power Query Editor, click Add Column > Custom Column.
6. In the Custom Column dialog box, enter the following formula: =Text.AfterDelimiter([Text], " ", [Text].Length - Text.PositionOf([Text], " ", [Text].Length - 1))
7. Click OK to create the custom column.
8. In the Power Query Editor, click Close & Load to load the data back into your Excel worksheet.

This Power Query formula uses the Text.AfterDelimiter function to extract the word that starts from the last space character in the text string.

Example:

Suppose you have the following text string in cell A1: "Hello World, this is an example sentence."

Using Power Query, you can extract the rightmost word as follows:

=Text.AfterDelimiter([Text], " ", [Text].Length - Text.PositionOf([Text], " ", [Text].Length - 1))

This formula will return the word "sentence".

Excel Text Manipulation Image Gallery

We hope this article has provided you with the information you need to extract the rightmost word in a text string in Excel. Whether you use the TEXTSPLIT function, FILTERXML function, RIGHT and FIND functions, VBA, or Power Query, there's a method to suit your needs. If you have any further questions or need additional assistance, please don't hesitate to ask.