REGEX Trim Data Parse Multi Name

[sslattery17]

Looking for some help with formula to reverse and parse data.  I have with the help of the community used different method successfully; but I am unable to get this to work on this workflow.

Sample data Set:

Resident

Celeste Junior, Carlos Eduardo
VACANT
Le, Jenny
Carrillo Victorio, Lizbeth

REGEX_Replace([Resident],"(.*), (.*)", "$2 $1") gets me to the below result

New Column

Primary

Carlos Eduardo Celeste Junior

VACANT
Jenny Le
Lizbeth Carrillo Victorio

Desired Result (two columns if there are 2 names.  I tried in two steps using trim right the second name from [Primary] to new column [Resident 2] cannot seem to make it work.

Primary                                        Resident 2

Carlos Eduardo                           Celeste Junior

VACANT
Jenny Le
Lizbeth Carrillo Victorio

Any suggestions would be greatly appreicated.

Thanks

SS

[OTrieger]

@sslattery17 

Why you are removing the comas? It seems that the comas are your delimiter. If you will use Text to Columns using coma as delimiter it will do what you are trying to do with RegEx.

[flying008 · Accepted solution]

Hi, @sslattery17 

[Primary]

REGEX_Replace([Resident],'([^,]+), ([^,]+)', IIF(CountWords([Resident]) <= 3, '$2 $1', '$2'))

[Resident 2]

IIF(CountWords([Resident]) <= 3, Null(), REGEX_Replace([Resident],'([^,]+), ([^,]+)','$1'))

Resident	Primary	Resident 2
Celeste Junior, Carlos Eduardo	Carlos Eduardo	Celeste Junior
VACANT	VACANT
Le, Jenny	Jenny Le
Carrillo Victorio, Lizbeth	Lizbeth Carrillo Victorio

[sslattery17]

@flying008  Thank you!  This is the solution I was looking for solving the double tenant rows into a 2nd resident column.  

@OTrieger as noted the issue with using text to column with comma as delimitator; were the rows with double names.  This created an additional step or two when putting the first and last names back together.