Remove specific attributes from the string of data and rearrange to new output

[quinburton1]

Hello, I am very much in need of assistance. I am struggling to find a solution to the following problem:

Raw Name	Cleaned Name - Objective
Smith, John	JOHN SMITH
Doe,JR.,John	JOHN DOE
Doe,III,Jane	JANE DOE
Smith, David L	DAVID SMITH
De Carolis,IV, Michael	MICHAEL DE CAROLIS
Doe Smith, John	JOHN DOE SMITH
Donte-Smith,SR,Michael L	MICHAEL DONTE-SMITH
Rodriguez,II,Hector M	HECTOR RODRIGUEZ

I need to do the following:

1. Remove all "JR", "JR.","SR","SR.","II","III,"IV" 

2. Remove the middle initial from the names that have the initial

3. Place the first name in-front of the last name and concatenate 

3. Capitalize and Trim 

Any help will be greatly appreciated. I have been spinning my wheels trying to figure this one out. I can knock it out in excel but I know Alteryx should be able to automate this process. 

[Luke_C]

Hi @quinburton1 

How's something like this? It works for the sample provided but be sure to test it on a wider set. Names can be tricky and this is a common ask on the community.

1. Split columns based on the comma
2. Text functions to trim, uppercase, concatenate the names together. 
   1. If the suffix is present, there is logic to handle that (based on how many fields were parsed)
   2. There is logic to only take the first name (getword()) for records with middle initials.

[binuacs]

@quinburton1 Another way of doing this

[sparksun]

I use Regex tool to sovle the problem