Titlecase - Keep Capital Letters after both Hyphens and Apostrophes

[amazz24]

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!  

[echuong1 · Accepted solution]

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

[MarqueeCrew]

@amazz24 ,

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!

[ThanyaWells123]

Is there any update on this?