Problem in Splitting Multi Part Name Field

Question

Hi, I had asked a question sometime back on this forum, in relation to using RegEx tool, for splitting a Name field into First Name, Middle Name and Last Name columns. Below is the expression i was using, ^(\<\w+\>)\s(.+)\s(\<.+\>)$ This is working fine for the 3 part Name field but not showing any result if the Name field just 1 or 2 part as Name value i.e. if it has a First Name or First and Middle Name. For ex, For a field like this, I get result like, Could someone suggest some solution to this problem? Thanks. Name.JPG

nick_ceneviva · Accepted Answer

If you are assuming that the only possible name formats are the ones shown in your screenshot, you could use the text to column tool and split on spaces (\s).  If there is only a first name listed, this will the first name column and leave the other two blanks.  Additional logic would need to be implemented if a name without a middle name was inputted.

Hope that helps!

jdunkerley79 · Accepted Answer

The Regex as you have it requires all 3 parts to be present.

I would go with @nick_ceneviva suggestion but if you want to do Regex then something like:

^(\w+)\W*((?<=\W)\w*)?\W*((?<=\W)\w*?)?$

should do what you need,