How can I capitalize job titles in Google Sheets while preserving all-cap acronyms?

Other Questions Google Sheets
1

I’m working with a list of job titles in Google Sheets and need a formula to capitalize the first letter of each word while leaving acronyms like CEO or AAPM in all caps. I attempted a formula using ARRAYFORMULA, TEXTJOIN, and REGEXMATCH to split and join the words correctly, but I’m receiving errors. Here’s the code I tried:

=ARRAYFORMULA(IF(A1:A<>"",
TEXTJOIN(" ", TRUE, IF(
REGEXMATCH(TEXT(SPLIT(A1:A, " ")), "(?i)^(JP|AASM|CEO|NASA|J.P.|FBI|US|ABM|AAST|AE)$"),
UPPER(SPLIT(A1:A, " ")),
PROPER(SPLIT(A1:A, " "))
)),
""))

Could someone suggest a fix or a better method? I’m new to these complex formulas and appreciate any help!

2

hey FlyingStar, have u tried using LAMBDA? it’s pretty powerful for this kinda stuff. here’s a quick formula that might work:

=LAMBDA(x,TEXTJOIN(" “,1,MAP(SPLIT(x,” “),LAMBDA(y,IF(REGEXMATCH(y,”[1]{2,}$"),y,PROPER(y)))))(A1:A)

it splits words, checks for all-caps, and rebuilds the string. give it a shot!

  1. A-Z ↩︎

3

I’ve encountered this issue before when working with employee databases. While the REGEXREPLACE solution is elegant, I found it occasionally missed some edge cases. A more robust approach I’ve used combines SPLIT, VLOOKUP, and TEXTJOIN:

=ARRAYFORMULA(IF(A1:A<>“”,
TEXTJOIN(" ", TRUE,
IFERROR(
VLOOKUP(
UPPER(SPLIT(A1:A, " ")),
{“CEO”,“CEO”;“NASA”,“NASA”;“FBI”,“FBI”}, 2, FALSE
),
PROPER(SPLIT(A1:A, " "))
)
),
“”))

This method allows you to maintain a separate list of acronyms, making it easier to update and manage. It’s particularly useful for organizations with industry-specific acronyms that may not follow common patterns.

4

I’ve faced a similar challenge with job titles in Sheets before. While your approach is on the right track, I found a simpler solution using REGEXREPLACE. Here’s a formula that worked for me:

=ARRAYFORMULA(IF(A1:A<>“”,
REGEXREPLACE(
PROPER(A1:A),
“\b([A-Z]{2,})\b”,
UPPER(“$1”)
),
“”))

This formula first uses PROPER to capitalize the first letter of each word, then uses REGEXREPLACE to find any words of two or more uppercase letters and keep them in all caps. It’s more flexible than listing specific acronyms and handles most cases well.

Remember to adjust the range (A1:A) to match your data. Hope this helps simplify your task!