Problem in Splitting Multi Part Name Field

[GauravRawal]

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.

[nick_ceneviva · Accepted solution]

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 solution]

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,