Nesting "RIGHT", "MID", and "FIND" in a formula

You are entitled to your opinion, but I disagree on each point. The formula is obtuse enough that the minor changes that you made will not be the difference between understandingt and not understanding.

 Here's my contribution:

=TRIM(RIGHT((SUBSTITUTE(C2," ","                                                                                    ")),45))

Not as sophisticated as what you all have done, but it does work.

BTW, just joined your forum. Thanks for making this available, I really appreciate it!  Am a long time user of Excel; just started writing macros about a year ago.  Will be taking a course in VB in a few weeks to increase my skills, and am really excited about that!

Glad to be here.

Gary

Garyb444:

=TRIM(RIGHT((SUBSTITUTE(C2," ","                                                                                    ")),45))

Not as sophisticated as what you all have done, but it does work.

It works when the last token (substring without whitespace) is 45 or fewer characters in length, fails when it's longer.

Yes, you're right.  I could increase that number to at least 75 and it would still work( as long as it doesn't exceed the number of spaces in the middle), but I normally use this to extract last names, and I haven't found any that would be as long as 45 characters.  

Thanks,

Gary