[LAST_NAME, FIRST_NAME MIDDLE_INITIAL] into [LAST_NAME, FIRST_NAME] but keep both

[matthew_swanson]

hello, i understand that i could do the name transformation using REGEX, but how do i create the new name format as an extra column instead of replacing the original column?

[Morgan_Thomas · Accepted solution]

Have you thought about just cloning the original column and then doing the regex on the new column? Attached an example of what I was thinking

[mpennington · Accepted solution]

I would recommend using a formula tool.

[Qiu]

@mpennington 
Thank you for showing the sample of placeholders of RegEx.
just one question is $2 necessary in this case? 😁

[mpennington]

@Qiu Good catch, sir.  It is not, I think I had thought to separate the first name and then forgot and left it in.  It isn't the most flexible bit of RegEx, it only works for the most basic of cases.  I wouldn't be surprised if there are going to be issues with Jr, Sr., III, etc.  I suppose the principle works, but the devil is in the details. 😁

[Qiu]

@mpennington 
Thank you for the detailed information.

Learned somethingn new from you. 😁

[matthew_swanson]

@Morgan_Thomas Thank you for the example! i wasn't aware that the formula tool could be used to generate a new column, that is exactly what i needed to know!

[matthew_swanson]

@mpennington Thank you! I was going to use the regex tool, but using the same formula tool that created the column is a great idea.

I used this regex string to remove the middle initial. You're right that this set of names contains first and last names with spaces in them