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.
Would Text-To-Column tool work?
Hi @shaheer , Try using Text-to-Columns by using delimiter '-' else if you want to use regex tool then please use below configuration.
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
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.
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.
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.
@shaheer try the formula, it is specific only for the above pattern
REGEX_Replace([Name], '(.*),(.*)\-(.*)', '$3 $1')