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:

A picture can tell a thousand words… Does the picture below give you the explanation? 🙂

So what do they do?

1. 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”.)
2. 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”
3. 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!