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.
We use the below list of 4 Excel user-defined functions to extract the last word.
- 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
RIGHT( text, [number_of_characters] )
- text – original text
- [number_of_characters] – optional. Number characters extract from the right.
Eg: RIGHT(“Filo**********Mix”) – returns *******Mix
- 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