Deleting Middle Names

[krishnagandhi]

So i am trying to do a join function but one set of data has middle names, while the other doesn't. What i am trying to do is delete the middle names from my smaller set of data so i can join it properly. How would i achieve this? I am guessing the RegEx tool?

Thanks!

[Blake]

Hi @krishnagandhi - it is hard to know exactly what you want without some sample data beyond a screenshot, but try out the attached regex tools and see if on works for you. You could also use a text to columns tool + a formula to concatenate the name if you aren't comfortable with Regex and your delimiter is always a comma. Do all you records contain this extra middle name or just some?

[ggruccio · Accepted solution]

Hi @krishnagandhi,

If that is the format of your data you could use the "Text to Columns" tool to parse it into three columns using the comma as the delimiter, then re-combine the columns with the first and last name only using a Formula tool.

May be a few more steps than using Regex, but should be effective.

[AbhilashR]

Hi! You could do it using Regex Tool, or a simple text-to-column as well. Attached is an implementation using text-to-column as a sample.