Use of Title Case vs RegEx for Last Names

[Ruben_Navarro]

Hi all - first post ever here... I've failed at finding a solution on the community so far, so hope you can help.

I have a large record of employee names and the primary issue is that some of them, they are all uppercase.

My first thought was to use TitleCase() but noticed this method does not work when names have a hyphen or apostrophe. Other cases are last names starting with "Mc" or "Mac"... or last names with "van der"...

Is there a relatively easy solution to this?

For the time being, I have used a Filter tool to separate those with uppercase names from the rest, and applied TitleCase to them, and used Union to bring back together all records. It solves most of the issues, but there are still some previously uppercase names that were hyphenated or had an apostrophe.

Thanks! 

[grossal]

Hi @Ruben_Navarro,

can you test the Data Cleansing Tool on your data? It worked for me:

Input:

Setting:

Output:

Let me know if it works on your data.

Best

Alex

[Ruben_Navarro]

Thanks for the suggestion @grossal

Unfortunately the Cleansing tool returns the same result as using TitleCase() through the Formula Tool. 

Sorry, I should have been a bit more specific with how the data looks. The issues appear with:

Jane McDoe -> Jane Mcdoe
Mickey Mouse-Disney -> Mickey Mouse-disney
Minnie O'Mouse -> Minnie O'mouse

... and so on.

Thanks again!

[DavidP · Accepted solution]

You could use a replace function first to replace "-" with " - ", "Mc" and "Mac" with "Mc " and "Mac " and so forth, the do Titlecase followed by another replace to get rid of the spaces.

I think you'll have to deal with your edge cases on an individual basis.

[grossal]

Thanks for the sample @Ruben_Navarro.

I agree with @DavidP. I think you need to handle this with custom ruling and a replace function.

[ThanyaWells123]

The above did not work for me can you please a formula