Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Titlecase - Keep Capital Letters after both Hyphens and Apostrophes

Highlighted
7 - Meteor

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!  

Highlighted
Alteryx
Alteryx

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

 

echuong1_0-1579714530390.png

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
7 - Meteor

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.

Highlighted
5 - Atom

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?

Labels