The GETLASTWORD user-defined function in Excel extracts the last word from a cell that contains a text string.
You can use built-in Excel functions to get the last word from a text string. However, instead of using multiple functions to create an advanced formula, you can use GETLASTWORD to make a simple, easy-readable formula.
Syntax
=GETLASTWORD(text)Arguments
The function uses a single, required argument, string1, a string-type variable.
GETLASTWORD Function Example
In the example, we have a text string in cell B3.
Formula:
=GETLASTWORD(B3)
Explanation:
First, the GETLASTWORD function uses the StrReverse VBA function to reverse the string. After that, with the help of the Instr function, find the first space in the reversed string.
Next, the built-in LEFT function extracts the left part of the reversed string up to that space. Then, as a result of the last string manipulation phase, the function switches the result using StrReverse.
The final step is to clean the result using TRIM. Now we have the last word of the cell without leading or trailing spaces.
VBA code
Copy the code into a new module: Press Alt+F11 to open the VBA editor. Create a new module and paste the code below:
Function GETLASTWORD(string1 As String)
Dim str As String
str = StrReverse(string1)
str = Left(str, InStr(1, str, " ", vbTextCompare))
GETLASTWORD = StrReverse(Trim(str))
End Function