topic Use of Title Case vs RegEx for Last Names in Alteryx Designer Desktop Discussions

Use of Title Case vs RegEx for Last Names

Ruben_Navarro — Thu, 02 Apr 2020 21:27:17 GMT

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!

Betreff: Use of Title Case vs RegEx for Last Names

grossal — Thu, 02 Apr 2020 21:32:15 GMT

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

Betreff: Use of Title Case vs RegEx for Last Names

Ruben_Navarro — Thu, 02 Apr 2020 21:38:25 GMT

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!

Re: Use of Title Case vs RegEx for Last Names

DavidP — Thu, 02 Apr 2020 21:41:22 GMT

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.

Re: Use of Title Case vs RegEx for Last Names

grossal — Thu, 02 Apr 2020 21:43:18 GMT

Thanks for the sample @Ruben_Navarro.

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

Re: Use of Title Case vs RegEx for Last Names

ThanyaWells123 — Mon, 06 Nov 2023 18:59:27 GMT

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