Parsing First and Last Name when there is a hyphen

Hi All,

I have a column of data that contains both the first and last name. I can use the example in the RegEx sample to easily parse most of it - except when it comes to names with hyphens.

I'm using the brackets to illustrate how I want the cells to appear:

[Joe-Schmo Johnson] needs to be [Joe-Schmo][Johnson]

I also get a NULL value if there is no first or last name; how can I replace a missing name with "N/A" or something to indicate it isn't there?

[Tom] becomes [NULL]

Thanks!!

Regex

Accepted answers

Jonathan-Sherman

Hi @Tessa,

I think I've got the complete solution for you now!

The workflow itself looks like this:

Input:

Output:

I've removed the "." periods in the names such as in rows 3 & 4 , however if you want to keep them then you will simply need to change the regex function to \u(?:[A-Za-z'\-\.]+)?

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workbook for you to download if needed.

Regards,

Jonathan

20191111 - ParsingNames.yxmd

All comments

BrandonB

What if you use a text to columns tool with \s as your delimiter (space)?

BrandonB

Also, if you want a value of N/A afterwards you can use a formula tool that updates the first name column that has been created and says IIF(IsNull([Field]), "N/A", [Field])

Tessa

Good call- this works most of the time...until I come across a customer with a middle initial.

Example: John J Smith

It's transforming it to [John] [J Smith]

I tried to put in a second text to columns tool and that works - but only when there is a middle initial! A customer without a middle initial will return a NULL value after the 2nd text to columns tool.

Is there a way to use a formula tool to trim off the first character that is followed by a white space? Something like that might do the trick...

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703