Use of Title Case vs RegEx for Last Names

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!

Expression

Accepted answers

DavidP

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.

All comments

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

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703