Resequencing data (Names) in a cell

[Livin404]

In Column A I have a list of names which should be Last name, First name, and Middle Name or Middle Initial or NMN if no Middle name exists. Some of the names will have a Suffix (Jr, Sr, II, III, IV).
Using my name as an example my name would be Whiteside Sheridan U IV. If I were to have it in Column A as Whiteside IV Sheridan U or the suffix any place other than the end. I would like to have a VBA when ran will resequence it as Whiteside Sheridan U IV. I don't need to have any message boxes letting me know it's correct, I would hope the VBA will not error out if there is nothing to correct. There are no punctuation in the names.

Thank you,

 

[Peter_SSs]

There are no punctuation in the names.

So you couldn't have O'Connor or Smith-Hall?

What about 15-20 rows of varied sample dummy data and the expected results with XL2BB?

 

[Livin404]

Thanks for getting back. I already have a code to remove punctuation. The dash in the surname is fine. This is specifically if there is a suffix it goes after the middle name. This data will eventually be saved as a CSV file to be uploaded into a an automated system we use. I have conditional formatting to identify the errors you mentioned. This just a specific macro to deal with incorrect placement of the suffix.

 

[Peter_SSs]

Still ..

What about 15-20 rows of varied sample dummy data and the expected results with XL2BB?