1

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?

player0
  • 124,011
  • 12
  • 67
  • 124
Digital Farmer
  • 1,705
  • 5
  • 17
  • 67
  • I would look into regular expressions to achieve that. Search with google on ‘google sheets regex’. You will find examples. – Gabe Jun 05 '22 at 20:15
  • Hi @Gabe the regex templates that my knowledge can understand, it is necessary to specify the sequence base in capital letters such as ```[^a-z]``` so that it can analyze, in this case, the use would be limited such as the list via ```SUBSTITUTE``` that I commented in relation to lowercase letters in other languages such as a letter ```ø``` – Digital Farmer Jun 05 '22 at 20:21
  • 1
    Maybe the excepted answer to this question can help you: https://stackoverflow.com/questions/20690499/concrete-javascript-regular-expression-for-accented-characters-diacritics/26900132#26900132 – Gabe Jun 05 '22 at 20:30

1 Answers1

2

Try:

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

enter image description here

Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
player0
  • 124,011
  • 12
  • 67
  • 124