How to do Titlecase without losing ALLCAPS words

[MIA1]

I have a set of data and the input can be inconsistent with upper and lower case. I would like to set the first letter of each word to be a capital, but without changing the words that are in ALLCAPS (which Titlecase changes). The strings are of variable length and number of words, and the ALLCAPS words also vary.

e.g

Now	Becomes ...
Red apple	Red Apple
red Apple	Red Apple
Red Apple	Red Apple
yellow Spotty ball	Yellow Spotty Ball
Yellow spotty ball	Yellow Spotty Ball
Blue DOG	Blue DOG
blue DOG	Blue DOG

Any help would be much appreciated!

[CharlieS · Accepted solution]

@MIA1

I have attached a solution that works by evaluating each word to see if it case-sensitive matches an uppercase forced version of itself.

Let me know if you have any questions!

[sreejithmp]

Hi

There is another way using data cleansing tool .

Please find the below

[BenMoss]

Hi!

As a leader in the Alteryx Community, I have the ability to identify & mark accepted solutions on behalf of community members - and recently did so on this thread. If you have any questions or concerns with the solution(s) I selected please let me know by replying to this post.

Learn more about Accepted Solutions here.

Thank you!

[mxledesma]

Hi

I saw your workflow but I do not understand the following:

IF Contains([Now],uppercase([Now]),0)!=-1 THEN titlecase([Now])
ELSE [Now] ENDIF.

I do not understand the what is inside the contains equals to -1?

Can you explain. 

Also, why is uppercase([Now],0) what is 0?

Michael Ledesma

[markcurry]

The part of the formula, Contains([Now],uppercase([Now]),0)  is simply checking if the field [Now] is all in uppercase or not.  If they are the same it will return -1 for True, or 0 for false.    So the full function sets it to Title case, if the field is not already in Upper case, otherwise leaves it as it is, (which will be in uppercase). 

The easiest way to see what's it doing is to put the Contains([Now],uppercase([Now]),0) in a separate formula tool.