Resequencing data (Names) in a cell

Livin404

Active Member
Joined
Jan 7, 2019
Messages
460
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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,
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,622
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jan 7, 2019
Messages
460
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,413
Messages
5,706,301
Members
421,441
Latest member
VapesRub

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top