Parse Employee Names with Different Formats

[bridgethutter]

Hello,

I have a number of employee names I am trying to parse into a standard format. I know no matter what, I need to parse at the first comma. And I am just trying to get rid of the middle initial at the end but not all names have it and some first names have a double name with either a space or - in between. I want it to end up as last name and first name, however I have so many different formats, I am having a hard time finding the right regex formula. 

Current:

Employee Name
HUNTER,BRIDGETT T

HUNTER WONDERLIN,BRIDGETT T

HUNTER-WONDERLIN,BRIDGETT

HUNTER,BRIDGETT LOUIS T

HUNTER, BRIDGETT-LOUIS T

HUNTER, BRIDGETT-LOUIS

Desired Outcome

HUNTER                                        BRIDGETT

HUNTER WONDERLIN                 BRIDGETT

HUNTER-WONDERLIN                 BRIDGETT

HUNTER                                        BRIDGETT LOUIS

HUNTER                                        BRIDGETT-LOUIS

HUNTER                                        BRIDGETT-LOUIS

Thanks!

Bridget

[Emmanuel_G]

@bridgethutter 

Find in attachement an easy regex expression to do that.

Is it ok for you ?

[MarqueeCrew · Accepted solution]

@bridgethutter ,

I think that the ask is how to remove a SPACE followed by a single LETTER which is at the end of the string.

.*(\s\u) is how you find that group with regex.

Regex_Replace([Name],"(.*)(\s\u)",'$1')

But you might want to avoid RegEx and use:

IF Length(Getword([Name],CountWords([Name])-1)) = 1
	THEN left([Name],Length([Name])-2)
ELSE [Name]
ENDIF

Cheers,

Mark

[DavidSkaife · Accepted solution]

Hi @bridgethutter 

While it's not 100% regex here is a way of doing it using Text to Columns and Regex:

[bridgethutter]

Thank you all for the help! @DavidSkaife and @MarqueeCrew both solutions worked. 

[bridgethutter]

Hi @Emmanuel_G  !

Thank you so much for the response! This worked great for all of them except for double first names.

Again, thank you for your help!

Bridget

[Emmanuel_G]

@bridgethutter 

Don't hesitate to mark answer as solution if it worked as it.