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?
- 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!
Thank you very much. Its really works for me right now. best wishes
You are welcome.
Glad it helps.
Agreed. Provided people are made aware of the limitations then there’s no harm in using a constant for that parameter.
Hi! I agree that, in general, a value for the REPT parameter of 100 should, in the vast majority of cases, be sufficient. However, that cannot be guaranteed, and so even better is to make this parameter dynamic, i.e. LEN(A2), which will ALWAYS be sufficient, the point being that no individual substring of the string in A2 can ever be longer than the string itself.
Hi XOR LX,
It’s my pleasure to have you commented here. I am really excited about it.
Good point! Totally agree with you.
I put a number here in order to make it easier for readers to understand the rationale behind. When we understand what the number does in the formula, it makes perfect sense to incorporate your suggestion of using LEN(A2). That makes the formula more robust.