Open In App
Related Articles

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

Improve Article
Save Article
Like Article

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()


REPT(text, number)


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

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



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


  • 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()


RIGHT( text, [number_of_characters] )


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

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

4. TRIM()




  • text – Removes leading and trailing spaces

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


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

Whether you're preparing for your first job interview or aiming to upskill in this ever-evolving tech landscape, GeeksforGeeks Courses are your key to success. We provide top-quality content at affordable prices, all geared towards accelerating your growth in a time-bound manner. Join the millions we've already empowered, and we're here to do the same for you. Don't miss out - check it out now!

Last Updated : 30 Nov, 2021
Like Article
Save Article
Similar Reads
Complete Tutorials