Learn **how to find and get the last word from a text string** using the **MID, FIND, CHAR, LEN**, and **SUBSTITUTE** functions.

Working with text strings in Excel is sometimes an easy task, sometimes not. In this article, we will explain how to use the best practices. As usual, first, we will perform the task using Excel’s built-in string manipulation functions. Furthermore, we’ll introduce the new user-defined function, **GetLastWord** too!

## How to extract the last word from a text string

**Steps to extract the last word from a text:**

- Replace the spaces with null strings:
**SUBSTITUTE** - Insert a delimiter character for the last space
**CHAR** - Find the delimiter position:
**FIND** - Strip the first character from the last word:
**LEN** - Extract the last word between delimiters:
**MID**

In the example, cell B3 contains the text string we want to manipulate. Use the following **formula** to find the last word:

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

**Explanation**

**Evaluate the formula from the inside out**.

First, we’ll replace the space characters with empty strings:

`=LEN(B3)-LEN(SUBSTITUTE(B3," ",""))`

After that, **insert a delimiter CHAR(1)** for the last space:

`=SUBSTITUTE(B3," ",CHAR(1),LEN(B3)-LEN(SUBSTITUTE(B3," ","")))`

**FIND the location** of the delimiter in the selected string:

`=FIND(CHAR(1),SUBSTITUTE(B3," ",CHAR(1),LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))`

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

`FIND(CHAR(1),SUBSTITUTE(B3," ",CHAR(1),LEN(B3)-LEN(SUBSTITUTE(B3," ","")))) + 1`

Finally, **expand the formula with the MID function** to extract the last word between the delimiter’s location:

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

## Get the last word using TRIM and RIGHT functions

In the next example, we have the same goal. If you want to extract the last word from a text string, you can use another alternative way to perform the calculation. Combine **REPT**, **SUBSTITUTE**, **RIGHT**, and **TRIM**. This method uses tricks and makes the formula mentioned above easier.

**Let us analyze the following formula:**

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

The **TRIM **function will remove any number of leading spaces. We are using 50 as an argument, but in some cases (for example, you have long words), you need to increase this number.

The **SUBSTITUTE** function finds all spaces in the text string and replaces each space with **N** spaces. **N** is an integer type variable, 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 step left. The **TRIM function removes extra spaces between words, 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

Last but not least: **Speed matters**. Of course, we have a user-defined function to boost your productivity. The syntax is easy to understand for everyone.

`=GetLastWord(cell)`

The function uses a single argument, “**cell reference**“.

We strongly recommend using a user-defined function library like **DataFX. The add-in dramatically improves your productivity**. Also, try **DataXL** **free excel add-in** for data cleansing projects. No coding skills are necessary; you only need to follow the add-in **installation guide**.