Extract Last 3 Words

[Sandeep Warrier]

Hey All,

Looking to extract the last 3 words in a string

RIGHT(SUBSTITUTE(LEFT(D21,LEN(D21)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(D21)," ",REPT(" ",99)),99)))-1)," ",REPT(" ",99)),99)&" "&TRIM(RIGHT(SUBSTITUTE(D21," ",REPT(" ",99)),99))

gives the last 2 words preceded by 99 spaces. It isn't letting me put a TRIM() before it. When I try to put a TRIM(), it says formula incorrect. Also, when I try to extend this to include the 3rd last word, it gives the same error, Formula Incorrect.

I'm sure there would be an easier way to do this. Even VBA would help.

Regards,
Sandeep.

 

[xld]

Try this

=MID(D21,FIND(CHAR(1),SUBSTITUTE(D21," ",CHAR(1),LEN(D21)-LEN(SUBSTITUTE(D21," ",""))-2))+1,99)

 

[PCL]

Perhaps next formula could help.
The principle is to install a particular character (here %) to mark the limit for the 3 last words

=RIGHT(A3,LEN(A3)-SEARCH("%",SUBSTITUTE(A3," ","%",LEN(A3)-LEN(SUBSTITUTE(A3," ",)) -2)))

 

[barry houdini]

Assuming a maximum word length of 30 characters try

=TRIM(RIGHT(SUBSTITUTE(D21," ",REPT(" ",90)),270))

 

[Sandeep Warrier]

Thanks a lot everyone... all 3 formulae worked.

xld.. very ingenious to replace the 3rd space from the right with another character and then find that character.

PCL.. your formula is also very similar to xld's.. xept it uses RIGHT instead of MID... again ingenious...

Barry.. I was trying out something like wot you gave but I think I was getting the numbers wrong... now that I understand how it works I can fiddle with the numbers to get the formula working for words with more than 30 characters (a rare scenario)... brilliant..

Once again thanks a lot...

1 last question... does anyone know why i wasnt allowed to put a TRIM before my formula. I know that my formula is very long, but usually if the formula is beyond excels limits it gives a formula too long error...

 

[barry houdini]

There's a limit of 7 nested functions, unless you have Excel 2007. Adding TRIM to what you have already breaks that limit........

 

[Sandeep Warrier]

Thanks Barry..

I was under the impression that the 7 nested functions restriction was mainly for IF statement.. I guess I was wrong... I'll keep that in mind for the future..

Regards,
Sandeep.

 

[barry houdini]

Many people only come up against that limitation using IF but it applies to any function or combination of functions. See Excel help "Specifications and limits".

Excel 2007 allows up to 64 nested functions......