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
Solved! Go to Solution.
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
While it's not 100% regex here is a way of doing it using Text to Columns and Regex:
Thank you all for the help! @DavidSkaife and @MarqueeCrew both solutions worked.
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