How to extract text from cell with Ultimate Suite for Excel As you have just seen, Microsoft Excel provides an array of different functions to work with text strings. In case you are unsure which function is best suited for your needs, commit the job to our Ultimate Suite for Excel.

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. Keynote is for mac or for windows.

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. 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!