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