Resequencing data (Names) in a cell

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
647
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,593
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

Well-known Member
Joined
Jan 7, 2019
Messages
647
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,176,412
Messages
5,902,948
Members
435,002
Latest member
chenmoti

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