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
Bookmarks