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

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.

11 Likes

100% Agreed!

I ended up working around the issue you are encountering with the calculated field by creating an automation that would take my calculated field & re-enter it in another field as plain text - I then use Field Tweaker to Proper Case my entries. The problem with this is that I am then waiting for the script to be applied across 10,000+ lines, rather then the formula tackling it from the start.

Very much needed! 80 views in two weeks, help us Airtable!

2 Likes

I’ll add to the chorus, would love this functionality.

Yes please, I just imported a bunch of data and realize it’s all screwy and need proper caps etc.

@Markus_Wernig @Joe_Svingala @Miles_Mattison @Christopher_Cantrall @Curtis_Gabhart_CCIM you can use the formula below (instead of creating many columns to achieve this):

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(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(LOWER({YOUR FIELD NAME}), "^a", "A"), "^b", "B"), "^c", "C"), "^d", "D"), "^e", "E"), "^f", "F"), "^g", "G"), "^h", "H"), "^i", "I"), "^j", "J"), "^k", "K"), "^l", "L"), "^m", "M"), "^n", "N"), "^o", "O"), "^p", "P"), "^q", "Q"), "^r", "R"), "^s", "S"), "^t", "T"), "^u", "U"), "^v", "V"), "^w", "W"), "^x", "X"), "^y", "Y"), "^z", "Z"), " a", " A"), " b", " B"), " c", " C"), " d", " D"), " e", " E"), " f", " F"), " g", " G"), " h", " H"), " i", " I"), " j", " J"), " k", " K"), " l", " L"), " m", " M"), " n", " N"), " o", " O"), " p", " P"), " q", " Q"), " r", " R"), " s", " S"), " t", " T"), " u", " U"), " v", " V"), " w", " W"), " x", " X"), " y", " Y"), " z", " Z")
8 Likes

My gosh! Thanks a lot!

That’s impressive, and yet somehow it makes me both happy and sad at the same time.

2 Likes

Apologies in advance if any of this doesn’t make sense, I’ve been playing with scripting, automations, and formulas for the past 6 hours.

I’m not sure exactly what your need is for Title Case, but I was determined to fix my issue regarding Title Case using a formula.

One of my bases is an ATS where people fill out an application/pre-screening questionnaire that has separate fields for First Name and Last Name. I was using a CONCANTENATE formula to have a Full Name field, but sometimes applicant’s input their names in all caps or all lower case.

The Formula:
UPPER(LEFT({First Name}, 1)) & LOWER(MID({First Name}, 2, 20)) & " " & UPPER(LEFT({Last Name}, 1)) & LOWER(MID({Last Name}, 2, 20))

I used UPPER to capitalize and LEFT to grab the first letter of the word in the First Name field. I then used LOWER to uncapitalize and MID to start with the second letter of the word in the First Name field. The 20 is probably more characters than I will ever have in that field, but I set it to pull apply to the next 20 letters just in case. Then I used ‘& " " &’ to add a space and then followed the exact same formula to edit the First Name fields and applied it to the Last Name fields.

Depending on your use case, you’ll probably need to edit other fields or formulas, or add/change some parameters, but I hope this is helpful!

3 Likes

Thanks so much for this! Now if we can just get a simpler function in Airtable …

Thank you for this! Extremely helpful and I hope I don’t run into a name where there is more than 20 characters in either parameter

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})
2 Likes

Chiming in to ditto this request.

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!