Name Parsing Question

Hello,

I am trying to use RegEx to parse out a column into First Name, Middle Name, & Last Name. I used the following in the RegEx tool:

(^[[:alpha:]]+)\s*([[:alpha:]]*)\s+([[:alpha:]]+$)

This works great for all except for the ones with a name such as Frances L Guida Dileo. I just get [Null] in my output fields with names such as this.

Does anyone have any suggestions on how I can change/update the expression above to put the remaining characters after the 2nd space into the Last Name column? Any help would be much appreciated.

RegEx Config.jpg

RegEx Example Output.jpg

Parse

Accepted answers

kvoelker

Hi All,

Thank you so much for all of the suggestions & help. I was able to combine @Thableaus suggestion with @JoBen suggestion to get the output that I need. Please see the screen shots below.

Name Parsing Fix.jpg

All comments

JoBen

@kvoelker, I think I was able to do what you needed using the text to columns tool delimiting on "\s" and leaving left overs in column three. I then just had to do a formula to swap the middle and last name for those that did not have a middle name listed. I've attached the workflow to show you what I came up with. Hope this helps.

Help1.yxmd

Thableaus

Hi @kvoelker

This Regex expression might work:

^(\w+)\s*(\w*)\s+(.*$)

or adapted to yours:

(^[[:alpha:]]+)\s*([[:alpha:]]*)\s+(.*$)

Instead of forcing the expression to have the last column LAST NAME with only alphabet characters, you change it to a dot, so it can also accept spaces.

Cheers,

kvoelker

Thanks! I'll try it.

kvoelker

Hi @Thableaus

These both worked well but it's still giving me incorrect results when I have a name with a suffix, such as John Doe Sr. Then my output has the correct first name, but the middle name is Doe & the last name is Sr. Any suggestions on how to ensure that doesn't happen? Thanks so much!

kvoelker

Hi All,

Thank you so much for all of the suggestions & help. I was able to combine @Thableaus suggestion with @JoBen suggestion to get the output that I need. Please see the screen shots below.

Name Parsing Fix.jpg

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703