How to Extract the Last Word From a Cell in Excel?

In this article, we explain how to extract the last word from a text in a cell using the Excel function.  Extracting words from a text is an important task in text processing.  

Eg.  Suppose we have a data file with a field called “Product_Category”, which was combined both product name and their respective category name with space as below.  Assume that the last word from the “Product_Category” field is a category.  We have to extract categories from the given data in column B for further analysis.

Sample Data:

We use the below list of 4 Excel user-defined functions to extract the last word.

1. REPT()

Syntax:

REPT(text, number)

Where,

• text – character to repeat
• number – number of times to repeat the character

Eg: REPT(“*“,10) – returns **********

2. SUBSTITUTE()

Syntax:

SUBSTITUTE( text, old_text, new_text, [instance_number] )

Where,

• text – the original text 
• old_text – text need to replace
• new_text –text replace with old text
• [instance_number] – Optional. The number indicates the instance number of old text to replace

Eg: SUBSTITUTE(“Filo Mix”,” “,REPT(“*”,10)) – returns Filo**********Mix

3. RIGHT()

Syntax:

RIGHT( text, [number_of_characters] )

Where,

• text – original text 
• [number_of_characters] – optional. Number characters extract from the right.

Eg: RIGHT(“Filo**********Mix”) – returns *******Mix

4. TRIM()

Syntax:

TRIM(text)

Where,

• text – Removes leading and trailing spaces

Eg: Eg: Trim(“Mix”) – returns Mix 

Implementation:

Follow the below steps to  Extract the last word from a Cell in Excel:

Step 1: Write header “Category” in cell B1.

Step 2: Write the below formula to cells “B2”.  In the given data category name is not more than 10 characters.  So we used 10 in both REPT() and SUBSTITUTE().  You can use any number greater than the maximum of the last word.

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",10)),10))

Step 3: Drag formula B2 to B14 to fill the same formula to all other cells