Excel 2007 : Suffix formula needed for the Jr, Sr, III in Excel 2007

You can't. There are just too many possible variations of names to come up with a rule to decide if the last word in a text string should be all caps or not.

It's easy to find a way to capitalise the last word in every instance, but then you'll end up with situations like

Kent C & Cathy Honda III
John & Mary BLOGGS
James C & Mary June HIGGINS
Barbara Taylor BRADFORD
Kim S & Su Wong II

etc.

You may want to use your formula as is and then send the spreadsheets through some Replace commands, like

replace Iii with III
replace Ii with II

Recently cross-posted at MrExcel.

http://www.mrexcel.com/forum/showthread.php?t=415393