Capitalize only words with more than 2 characters on Google Sheets-CodePudding

How can I capitalize only words with more than a specific count of characters? Google Sheets formula PROPER capitalizes all the words without any exclusions. For example, I would like to omit the capitalization of an acronym such as "PC" or "RAM" that is contained within a string in a cell.

CodePudding user response：

Capitalize All and exclude from a list

To be able to

Capitalize only words more than 2 charachters long

=ArrayFormula(IF(A2:A="",,
              IF(LEN(UPPER(A2:A))<=2,
                 UPPER(A2:A),PROPER(A2:A))))

CodePudding user response：

I may have been overthinking this by a mile, but try:

Formula in B1:

=INDEX(SUBSTITUTE(TEXTJOIN("",0,IF(LEFT(SPLIT(REGEXREPLACE(A1,"\b(RAM|[A-Za-z]{1,2})\b","|♣$1|"),"|"))="♣",SPLIT(REGEXREPLACE(A1,"\b(RAM|[A-Za-z]{1,2})\b","|♣$1|"),"|"),PROPER(SPLIT(REGEXREPLACE(A1,"\b(RAM|[A-Za-z]{1,2})\b","|♣$1|"),"|")))),"♣",""))

The point here is that \b(RAM|[A-Za-z]{1,2})\b would capture any 1-2 character word made of word-characters between word-boundaries or RAM. Now you can add any exclusion into the alternation of the pattern through concatenating more |. The replacement includes a backreference to the capture group to encapsulate the substring between a delimiter to split on and a leading unique character. The IF() will then check whether or not any element from the resulting SPLIT() needs to be processed with Proper() or not.

Note: Word-boundaries like \b may not be safe when you'd have data like hello-pc and you'd want this to be processed with PROPER(). A small adjustment to the formula is then needed.