Apply a custom Case for String (Not Capital, Lowercase or TitleCase?)

Question

Hi everyone,

Looking for guidance applying custom casing for a String value.

Example:

I have "Ts Portfolio Journal" by use of a TitleCase within the cleanse filter.

Looking for TS Portfolio Journal.

"Gts Portfolio Journal" to GTS Portfolio Journal.

What tool would I use to convert these fields?

I would imagine this uses Regex and Case within a formula.. but just a wild guess as I'm not sure.

Thank you Community,

Waqar

casing.PNG

robheb · Accepted Answer

Are you only looking to capitalize "Ts" and "Gts"?

If there are only two possible strings, it might be simplest just use the "Replace" function in the formula tool. You'll want to be careful as this will change all instances of the letter pattern. Ex:

This could also be done with the RegEx tool using the "Replace" output method. Again, this will change all occurrences of the letter combinations.

If there are many different strings that you want converted and, as seen in your attached image, they can occur at different parts of the string, we might need to get more creative. Can you give a little more information about additional text that might need to be capitalized?

david_fetters · Accepted Answer

This should work for cases where you wish to capitalize the first word in your string field, provided that the string does not start with a space and that the word you wish to capitalize is a single word:

In a formula tool (replacing [Field1] with the field name containing your text) write the following:

UPPERCASE(LEFT([Field1],FindString([Field1], " "))) + 
" " +
Right([Field1], Length([Field1])-FindString([Field1], " ")-1)

This is some good old fashioned excel munging that splits the string into two substrings around the first " " space character it finds.  The "-1" at the end is due to the 0 based index of the FindString function.  Works for your test cases