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

[waqaraphoto]

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

[robheb · Accepted solution]

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 solution]

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

[waqaraphoto]

Hi David,

Thank you for your prompt response. I will use this for this case and hybrids of it for other cases. This is a great starting point! Thanks a lot!

[waqaraphoto]

Hi Roheb,

this was the solution I used in my case, Thank you for the screenshots! 

Luckily I think this is good enough in my case because its highly unlikely that we will see additional combinations/order of these strings with similar letters (They are hashtags that are used within our Leankit KanBan Board). 

Thanks again! 

Waqar