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.
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.
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:
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!
Preface: Though I'm late to the thread, I share your consternation (as do many). After an utterly disappointing attempt to have Airtable AI generate a formula that would efficiently solve this formatting pain point, which produced invalid and baffling results (including generating formulae functions that don't exist in AT, and then generating invalid "fix suggestions" to correct its own errors and fabrications), I gave up on elegance and went back to my layman, multiple formula method—I call it chunk-n-clunk. It's not particularly streamlined or pretty, but hey, it works. I'm including it here in case anyone else in the "no code" camp just needs something that gets the job done.
Our Use Case: My company (a music publisher and record administrator) receives a continual influx of large datasets from multiple external sources. These sources return values in UPPER CASE, but we use and display values in Title Case. Automations are not a viable solution because of volume, and text formatting capabilities.
Solution: 3-formula sequence, where the 3rd formula results in text displayed in Title Case.
{FORMULA 1}
LOWER({FIELD WITH UPPERCASE TEXT})
//
RESULT: "SONG TITLE HERE" -> "song title here"
{FORMULA 2}
UPPER(LEFT({FORMULA 1}, 1)) & LOWER(MID({FORMULA 1}, 2, LEN({FORMULA 1}) - 1))
//
RESULT: "song title here" -> "Song title here"
*Others have suggested the REGEX_REPLACE function in various iterations, so thanks to all who gave examples. I pared the above down from previous examples, and preceded it with the other 2 formulae to get it to work as expected for our use-case.
Conclusion: If you have a volume of varied text strings that ultimately needs to be formatted in Title Case (AKA Proper Case), you can achieve it in 3 formula steps by
Converting all text of your {FIELD WITH UPPERCASE TEXT} (or {Field with Text}, however it appears) to lowercase: {FORMULA 1}
Capitalizing the first letter of the first word of the preceding formula's calculation: {FORMULA 2}
From {FORMULA 2}'s calculation, replacing any single space followed by a lowercase letter with a single space followed by a capital letter: {FORMULA 3}
There is probably a better way to achieve this, but I couldn't find one. SO, if you're stuck like I was, hopefully this can help.
I just wanted to add a note that I thought I found a simpler solution to this problem by using the uppercase flag in the Regex replacement string like so:
