Name Sorting in VBA

I am having an issue with finding a stable, consistent formula that will sort name by:

lastName, firstName middleInitial.
so…
John Q. Smith
becomes…
Smith, John Q.

And to throw in the curve ball I need the formula to account for titles!

Mr. John Q. Smith Jr.
becomes
Smith Jr., Mr. John Q.

This should work whether there is punctuation or not!

This is what I have so far but for some reason it does not work 100% of the time (should note that this code does not account for titles in name)

=IF(A83<>“”,(RIGHT(B83,LEN(B83)-FIND(“*”,SUBSTITUTE(B83,” “,”*”,LEN(B83)-LEN(SUBSTITUTE(B83,” “,””)))))&”, “&LEFT(B83,FIND(“*”,SUBSTITUTE(B83,” “,”*”,LEN(B83)-LEN(SUBSTITUTE(B83,” “,””))))-1)))

Thank you all in advance!