Search code examples
regexgoogle-sheetsreplacegoogle-sheets-formulare2

Remove lowercase letters from city names to generate acronyms if the names contain multiple words on Google Sheets

City names that contain multiple words I want to become acronyms from your capital letters:

Rio de Janeiro
São Paulo
Osnabruck
Brøndby County

Expected Output:

R.J.
S.P.
Osnabruck
B.C.

I understand that to do this I first have to SPLIT the words with " ", remove the lowercase letters and then JOIN with ".":

=IF(FIND(" ",A1),JOIN(".",SPLIT(A1," ")),A1)

But I don't know how I can remove the lowercase letters to leave only uppercase letters.

Note: I thought of using SUBSTITUTE(...,{all lowercase alphabet list},"") or use REGEX like a [^a-z], but there is a problem, if there are letters from other languages that are not used in my language, there will always appear a lowercase letter or other.

For example:

ø

Is there a way to separate without specifying letter by letter in a list to substitute or basic regex thath contains only basic language for english and portuguese?

Solution

  • Try:

    =ARRAYFORMULA(IF(REGEXMATCH(A1:A4, "\s"), 
 SUBSTITUTE(TRIM(REGEXREPLACE(A1:A4, "[^A-Z ]", )), " ", ".")&".", A1:A4))

    enter image description here