Get last word from a text string

This post explains how to get the last word from a text string using standard excel formulas. We’ll introduce the new GetLastWord function too! And we’ll also introduces some minor modifications regarding formulas.

Generic Formula to get the last word a text string

The first example will show you how to find the last word in cell A2.

=MID(A2,1+FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1), LEN(A2)-LEN(SUBSTITUTE(A2," ","")))),LEN(A2))

Follow these simples steps to extract the last word:

  1. Count the number of spaces
  2. Change the last space in a cell to a delimiter (special character)
  3. Find that non-printing characters
  4. Extract the part of the string from the delimiter until the end of the string

How this formula works

Take a deep dive into the explanation of the formula!

extract the last word from a string using formulas excel

As first we’ll replace the spaces to null strings.

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))

After that, we’ll insert a delimiter CHAR(1) for the last space.

=SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ","")))

FIND the location of delimiter in that string:

=FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))

Because we are using a delimiter, we have to strip the first character of the last word.

FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ","")))) + 1

Finally, expand the formula with the MID function to extract the last word between the delimiters location and the end of the text.

=MID(A2,1+FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ","")))),LEN(A2))

Get the last word using TRIM and RIGHT functions

If you want to extract the last word from a text string, you can use another function based solution. Use the combination of REPT, SUBSTITUTE, RIGHT, and TRIM.

Let us analyze the following formula:

=TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",50)),50))
TRIM RIGHT SUBSTITUTE functions in EXCEL

The formula return with “text”

The TRIM function will remove any number of leading spaces. We are using 50 as a parameter. In some cases, (for exampleif you have long words), you need to increase this number.

To find all spaces in the text string, we’ll use the SUBSTITUTE function. The function replaces each space with N spaces. N is an integer, in this case, 50.

=SUBSTITUTE(A3," ",REPT(" ",50))

The RIGHT function extracts N characters (50) from the right.

=RIGHT(SUBSTITUTE(A3," ",REPT(" ",50)),50)

We have only one operation left. The TRIM function removes extra spaces between words, extra leading, or trailing spaces. Finally, the formula returns the last word.

=TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",50)),50))

Extract the last word using UDF and VBA

Of course, we have a user-defined function to speed up your productivity.

The syntax is easy to understand for everyone.

=GetLastWord(cell)

We need only add a single argument, cell reference.

This is more than enough!

how to use the GET LAST WORD function in Excel

We strongly recommend you using user-defined functions and formulas in Excel. No need for a developer. Use DataXL free excel add-in for data cleansing! No coding skills necessary!

Related Formulas and Examples