Return last word from a string
This tutorial shows how to return the last word from a string using an Excel formula or VBA
Example: Return last word from a string
|
=TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",1000)),1000))
|
This formula uses a combination of the Excel TRIM, RIGHT, SUBSTITUTE and REPT functions to return the last word in a string.
|
Sub Return_last_word_from_string()
'declare variables
Dim ws As Worksheet
Dim val As String
Dim ws As Worksheet
Dim val As String
Set ws = Worksheets("Analysis")
val = ws.Range("B5")
val = ws.Range("B5")
'return the last word from a string
ws.Range("C5") = Right(val, Len(val) - (InStrRev(val, " ")))
ws.Range("C5") = Right(val, Len(val) - (InStrRev(val, " ")))
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
String: Select the string from which you want extract the last word by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the string from which you want to extract the last word by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
String: Select the string from which you want extract the last word by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the string from which you want to extract the last word by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Sub Return_last_word_from_string()
'declare variables
Dim ws As Worksheet
Dim lastwrd As String
Dim ws As Worksheet
Dim lastwrd As String
Set ws = Worksheets("Analysis")
On Error Resume Next
'return the last word from a range of strings
For x = 5 To 10
ws.Range("C" & x) = Right(ws.Range("B" & x), Len(ws.Range("B" & x)) - (InStrRev(ws.Range("B" & x), " ")))
Next x
For x = 5 To 10
ws.Range("C" & x) = Right(ws.Range("B" & x), Len(ws.Range("B" & x)) - (InStrRev(ws.Range("B" & x), " ")))
Next x
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output column range by changing the column reference ("C") and the row range by changing the row reference which are the values that are assigned x in the VBA code.
Range: Select the range from which you want to return the last word from each cell by changing the column and row references in the VBA code. The column reference is represented by letter "B" and the row reference is represented by the values that are assigned to x which are 5 to 10. Therefore, the range that this code will loop through will be ("B5:B10"). The row numbers are represented by the "x" value which can also be changed.
Worksheet Selection: Select the worksheet which captures the range from which you want to extract the last word by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output column range by changing the column reference ("C") and the row range by changing the row reference which are the values that are assigned x in the VBA code.
Range: Select the range from which you want to return the last word from each cell by changing the column and row references in the VBA code. The column reference is represented by letter "B" and the row reference is represented by the values that are assigned to x which are 5 to 10. Therefore, the range that this code will loop through will be ("B5:B10"). The row numbers are represented by the "x" value which can also be changed.
Worksheet Selection: Select the worksheet which captures the range from which you want to extract the last word by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial explains how to return the last word from a string using an Excel formula and VBA.
The Excel method uses a combination of the Excel TRIM, RIGHT, SUBSTITUTE and REPT functions to return the last word in a string.
Both of the VBA methods make use of the Right, Len and InStrRev functions, with the (" ") criteria to return the last word from a string. The first method shows how the VBA code can be applied if you are only looking to return the last word from a single cell. The second VBA method can be applied if you want to return the last word from multiple cells, where the VBA code will loop through each cell in the specified range and return the last word from each cell.
FORMULA
=TRIM(RIGHT(SUBSTITUTE(string," ",REPT(" ",1000)),1000))
=TRIM(RIGHT(SUBSTITUTE(string," ",REPT(" ",1000)),1000))
ARGUMENTS
string: The string from which you want to retrieve the last word.
string: The string from which you want to retrieve the last word.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return first word from a string | How to return the return the first word from a string using Excel or VBA | |
| Return most frequently occurring text | How to return the most frequently occurring text using Excel or VBA | |
| Return last numeric value in a column | How to return the last numeric value in a column using Excel or VBA | |
| Return last numeric value in a row | How to return the last numeric value in a row using Excel or VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| SUBSTITUTE Function | The Excel SUBSTITUTE function replaces characters with another in a specified string | |
| RIGHT Function | The Excel RIGHT function returns the specified number of characters from a specified string, starting from the right side | |
| TRIM Function | The Excel TRIM function removes the spaces at the start and end of the text and also removes the unnecessary spaces between words, leaving only a single space between words and numbers that form part of the text |