Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

We still need a simple TITLE CASE (PROPER) function alongside UPPER and LOWER

cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Markus_Wernig
8 - Airtable Astronomer
8 - Airtable Astronomer

Users have been calling for a simple TITLE CASE / PROPER function since 2016.

And I know, there is @Bill.Frenchโ€™s wonderful script - Field Tweaker - that allows you to reformat a field as either UPPER, LOWER, or TITLE CASE.

However, the script doesnโ€™t work when the field is a computed field, as it is in my case.

The fields I need text formatting control over are computed from different sources. I need versions of those fields that are UPPER CASE - which is easily achieved via the UPPER() function - but I also need TITLE CASE versions (for reasons I donโ€™t want to bore anyone with).

Again, if there was a standard PROPER() function in Airtable, this would be a piece of cake.
I am sure I am not alone with this request.

14 Comments
Tim_Mackey
6 - Interface Innovator
6 - Interface Innovator

Thanks for this function, @gxjansen! I created my own modified version of it thatโ€™s a little more flexibleโ€”the original formula wasnโ€™t able to handle symbols like parentheses, hyphens, and quotation marks, which my new formula does. Additionally, I cut the number of REGEX_REPLACE() functions in half by using multi-option capturing groups. Now only a single REGEX_REPLACE() function is needed for each letter of the alphabet.

Example

Hereโ€™s a super contrived example:

The โ€œtrickโ€ to โ€œbeingโ€ [healthy] (and happy!) and โ€˜wiseโ€™ {forever} โ€˜we promiseโ€™

Hereโ€™s what you would get with the old formula (i.e. many words arenโ€™t capitalized):

The โ€œtrickโ€ To โ€œbeingโ€ [healthy] (and Happy!) And โ€œwiseโ€ {forever} โ€˜we Promiseโ€™

Hereโ€™s what you get with the new formula:

The โ€œTrickโ€ To โ€œBeingโ€ [Healthy] (And Happy!) And โ€œWiseโ€ {Forever} โ€˜We Promiseโ€™

Complete function:

Copy and paste the complete function below into your formula, and replace {YOUR FIELD NAME HERE} with the correct field name.

REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
REGEX_REPLACE(
{YOUR FIELD NAME HERE},
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)a", "$1A"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)b", "$1B"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)c", "$1C"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)d", "$1D"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)e", "$1E"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)f", "$1F"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)g", "$1G"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)h", "$1H"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)i", "$1I"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)j", "$1J"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)k", "$1K"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)l", "$1L"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)m", "$1M"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)n", "$1N"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)o", "$1O"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)p", "$1P"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)q", "$1Q"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)r", "$1R"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)s", "$1S"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)t", "$1T"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)u", "$1U"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)v", "$1V"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)w", "$1W"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)x", "$1X"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)y", "$1Y"),
"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)z", "$1Z")

If the formatting of the formula is giving you trouble, hereโ€™s the formula compressed onto a single line:

REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE(REGEX_REPLACE({YOUR FIELD NAME HERE},"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)a", "$1A"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)b", "$1B"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)c", "$1C"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)d", "$1D"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)e", "$1E"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)f", "$1F"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)g", "$1G"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)h", "$1H"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)i", "$1I"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)j", "$1J"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)k", "$1K"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)l", "$1L"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)m", "$1M"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)n", "$1N"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)o", "$1O"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)p", "$1P"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)q", "$1Q"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)r", "$1R"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)s", "$1S"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)t", "$1T"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)u", "$1U"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)v", "$1V"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)w", "$1W"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)x", "$1X"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)y", "$1Y"),"(^| |\(|\[|\{|-|\"|[ ^]\'|โ€œ|โ€˜)z", "$1Z")

Notes

Each regex function has the following capture group:

(^| |\(|\[|\{|-|\"|[ ^]\'| โ€œ| โ€˜)

This is a list of symbols, separated by the pipe symbol (|) which means โ€œorโ€. You can add or remove symbols as you please, always separating them with a pipe. Hereโ€™s the list of symbols Iโ€™m currently matching on with explanations:

^        โ† Line start
\(       โ† Parentheses
\[       โ† Square Bracket
\{       โ† Curly Brace
-        โ† Hyphen, ensures that hyphenated words are properly capitalized, e.g. Empty-Handed
\"       โ† Double quote
[ ^]\'   โ† Single quote. must be preceded by a space or at line beginning, this is to avoid matching apostrophes
 โ€œ       โ† Double curly quote
 โ€˜       โ† Single curly quote

Important: this formula no longer converts the original text to lower case before capitalizing. I made this decision because there are many cases when doing this would result in incorrect capitalization. A couple examples:

  • Family names: McCabe would become Mccabe if we converted to lowercase first
  • Common acronyms: LED, DIY, ATM would become Led, Diy, Atm

If the input text youโ€™re dealing with has a lot of ALL-CAPS you want to get rid of and you donโ€™t care about incorrect capitalizations like the one above, you can put the initial lowercase transformation back in by adding LOWER() around the field name, like so:

LOWER({YOUR FIELD NAME HERE})
Melinda_Lee
4 - Data Explorer
4 - Data Explorer

Chiming in to ditto this request.

Shayne_Bundy
6 - Interface Innovator
6 - Interface Innovator

Is there a way to adjust this for situations where there is a space in the {First Name} field? Such as if they had a first and middle name entered there, both in all caps, and I wanted to correct it?
Thank you!

claimdepot
5 - Automation Enthusiast
5 - Automation Enthusiast

Please please please implement this @ Airtable