Regular Expression for the Following Request:

[shaheer]

I have a name in a column like so:

Ann, Lou-Mary.

I am able to change the name so that it becomes the following:

Lou-Mary Ann.

Now, I want the name to be like so:

Mary Ann.

I've tried experimenting with some different regular expressions but have had no success. 

[MilindG]

Would Text-To-Column tool work?

[grazitti_sapna]

Hi @shaheer , Try using Text-to-Columns by using delimiter '-' else if you want to use regex tool then please use below configuration.

[Ben_H]

Hi @shaheer,

This should work on your original data (i.e. Ann, Lou-Mary. )

REGEX_Replace([Name],"(.*),.*-(.*)\.","$2"+" "+"$1")

It does seem very specific though, are all the other names in the column formatted the same way?

Regards,

Ben

[shaheer]

Sorry, forgot to mention that the name occurs several times in multiple columns. This would work but I would need to use the tool several times. 

[binu_acs]

@shaheer One way of doing this

[shaheer]

Yes it is just a few names out of thousands, so yeah it's specific. This name also appears in multiple columns. I have tried using this formula you gave me but it appears to move around the names and keep the formula. For example, Lou-Mary Ann became Lou Ann-Mary.

[shaheer]

I tried using your formula but the name did not change for some reason. Would it be because I am using a multi field formula and then formula tool in sequence?

The multi field formula tool is the one that is rearranging the names that are in lastname, firstname format. Next, I just want to clean up the names like Lou-Mary Ann.

[binu_acs]

@shaheer try the formula, it is specific only for the above pattern

REGEX_Replace([Name], '(.*),(.*)\-(.*)', '$3 $1')