There are many cases that we may just want the last word from a text string. Typical example would be something like the last row in the above screenshot: There are many codes at the end of a text string and we just want to focus on the codes… If you need to deal with this, the following formula may help you:
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",10)),10))
Let’s take the text string in A2 “Today is beautiful” for illustration:
So what do they do?
- SUBSTITUTE(A2,” “,REPT(” “,10)) simply replaces a single space by 10 spaces. It returns “* beautiful” as a result (which is a text string followed by 10 spaces then by the word “beautiful”.)
- Wrapping the above result by RIGHT(text,10) tells Excel to extract the 10 rightmost letters from the text string. In this case, it is ” beautiful”
- TRIM(” beautiful”) does the simple job to remove all leading spaces. As a result, we get “beautiful” as desired.
Note: For illustration purpose, all last words in the above example are within 10 letters. You may already spot that for a word like “illustration”, Step 2 would give you an incorrect result of “lustration”. To fix this, just revise the number 10 to a bigger number in the formula. A number of at least the longest length of the last words. In general, 100 is long enough. Isn’t it?
Syntax and description of the Functions involved:
- REPT(text,number_times) – Repeats text a given number of times
- SUBSTITUTE(text,old_text,new_text,[instance_num]) – Substitutes new_text for old_text in a text string
- RIGHT(text,num_chars) – Returns the last character or characters in a text string, based on the number of characters you specify
- TRIM(text) – Removes all spaces from text except for single spaces between words
Again, combination of functions brings the magic to live!