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!
