Do you need to extract the last word from your customer name or product? This Post explains to you how to extract the last word from a string. It is one of the most important tasks in text processing to split a string.
Sometimes you may receive data with joint text with a delimiter. To extract the last word from a cell string, you have to use combinations of excel formulas TRIM, RIGHT, SUBSTITUTE and REPT.
TRIM(text) – to remove the additional spaces
Eg: Trim(“ pptexcel”) – returns pptexcel
The Excel TRIM function can be used to clean up text in a spreadsheet that has irregular spacing. This function removes all spaces, with the exception of single spaces between words.
RIGHT( text, [NumOfCharacters] ) – extract number of character from right side of the given text
Eg.Right(“Rui~~~~~Costa”,5) – returns Costa
The Excel RIGHT function returns the number of characters on a string’s right side as a text string. Depending on the number of characters they select, this method allows users to extract the last characters in a text string.
SUBSTITUTE( text, old_text, new_text, [instance] ) – substitute function find the single space and replace with tild symbol
SUBSTITUTE(“Rui Costa”,” “,REPT(“~”,5)) – returns Rui~~~~~Costa
The Excel SUBSTITUTE function replaces an old_text with a new_text.
REPT(text, integer) – repeat the given text with the number of times
eg: REPT(“~“,5) – returns ~~~~~
Suppose you have a list of food ball player names and need to extract the last word from the list.
Please follow the below steps to extract the last word from each cell
- Type header Last word in cell B1 and format as cell A1
- Type the below formula in cell B2
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50))
- Drag till cell B10, to fill the formula to extract the last word from each cell
Step-by-step evaluation of the formula on cell B2
=TRIM(RIGHT(SUBSTITUTE(A2,” “,REPT(” “,50)),50))
| Replaced | Output |
|---|---|
| A2 | TRIM(RIGHT(SUBSTITUTE(“Cristiano Ronaldo” ,” “,REPT(” “,50)),50)) |
| REPT(“~”,50) | TRIM(RIGHT(SUBSTITUTE(“Cristiano Ronaldo” ,” “, “~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~“),50)) |
| SUBSTITUTE(“Cristiano Ronaldo”, ” “, “~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~”) | TRIM(RIGHT(“Cristiano~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Ronaldo“,50)) |
| RIGHT(“Cristiano~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Ronaldo”,50) | TRIM(“~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Ronaldo“) |
| TRIM(” Ronaldo”) | Ronaldo |
Conclusions
Extracting the last word from the text is a type of text processing. You can use Excel Text to columns to extract the last word if your text contains any pattern like two or three texts.
