Titlecase - Keep Capital Letters after both Hyphens and Apostrophes

Hi All - I am trying to maintain consistency between about 1,500 names in my data set. The names include hyphens, such as Ann-Marie and apostrophes, such as O'Connor. Using just a titlecase formula converts the names to "Ann-marie" and "O'connor" respectively. I tried to use a formula to correct the issue, but was only able to fix 1 of the 2 issues. The formula I used to fix hyphenated names is shown below, however, I cannot seem to figure out how to amend this to also work with the apostrophe issue. I also tried to use Regex replace formulas but I was unsuccessful in deciphering how the formula works.

Replace(TitleCase(ReplaceChar([Manager Lvl 2], "-", '$')), '$',"-")

Any help is greatly appreciated!

Regex

Preparation

Accepted answers

echuong1

You can use a conditional statement to check if the value contains an apostrophe or hyphen. If they do, then the replace formula is used. Otherwise, a standard titlecase formula is used. See attached for an example.

if contains([Manager Lvl 2], "-") then
Replace(TitleCase(ReplaceChar([Manager Lvl 2], "-", '$')), '$',"-")
elseif contains([Manager Lvl 2], "'") then
Replace(TitleCase(ReplaceChar([Manager Lvl 2], "'", '$')), '$',"'")
else
TitleCase([Manager Lvl 2])
endif

New Workflow5.yxmd

All comments

How about this:

IF 
	REGEX_Match([Name], "\w+\s\w+")	THEN	TitleCase(GetWord([Name], 0)) + " " + TitleCase(GetWord([Name], 1))
ELSEIF 
	REGEX_Match([Name], "\w'\w+")	THEN	Uppercase(Left([Name], 1)) + "'" +  TitleCase(Substring([Name],2))
ELSEIF
	REGEX_Match([Name], "\w+-\w+")	THEN	TitleCase(left([Name],FindString([Name], "-"))) + "-" +
											TitleCase(Substring([Name],FindString([Name], "-")+1))
ELSE
TitleCase([Name])
endif

It should handle:

o'toole, ann-marie, and ann marie.

Cheers,

Mark

amazz24

Hi @echuong1 and @MarqueeCrew - Thank you both for the quick replies to my post. The response from @echuong1 worked perfectly!

I decided to also try the solution provided by @MarqueeCrew as I would like to learn more about using Regex formulas, but found that it did not correct either instance of the issue in my dataset. I was also unable to follow the logic with the proposed solution due to my inexperience so I could not even give an attempt to rework the formula.

Harlow999

I am glad that others were struggling with this feature of Data Cleansing, maybe it can be addressed in a future release.

I also have the scenario where Alteryx changes "McDonald" to "Mcdonald", "McClure" to "Mclure" and DeSouza" to "Desouza".

What's the best way to re-capitalize the third character?

angeline559

Hello @amazz24,

I ran across your formula while researching a similar challenge. Your formula has just what I needed.

I just replace my field and the "-" with "/" for my issue and it worked beautifully. I wanted to say thanks!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703