Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Titlecase - Keep Capital Letters after both Hyphens and Apostrophes

amazz24
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!  

6 REPLIES 6
echuong1
Alteryx Alumni (Retired)

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

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
amazz24
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.

Harlow999
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?

angeline559
6 - Meteoroid

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
5 - Atom

Is there any update on this? 

Labels