Use REGEXEXTRACT() to extract only upper case letters from a sentence in Google sheets

user_6827197 0
Viewed 41

I'm thinking this should be basic but having tried a number of things I'm nowhere nearer a solution:

I have a list of names and want to extract the initials of the names in the next column:

Name (have) Initials (want)
John Wayne JW
Cindy Crawford CC
Björn Borg BB
Alexandria Ocasio-Cortez AOC
Björk B
Mesut Özil MÖ

Note that some of these have non-English letters and they may also include hyphens. Using REGEXMATCH() I've been able to extract the first initial but that's where it stops working for me. For example this should work according to regex101:

=REGEXEXTRACT(AH2, "\b[A-Z]+(?:\s+[A-Z]+)*") but only yields the first letter.

google-sheets regex
3 Answers

-edit for anyone showing up here-

Please have a look at the comment to the question. It has the best answer to this problem, by using Regexreplace() rather than Regexextract() to target anything that is not an upper case character and applying the relevant unicode pattern.

-/ edit -

Finally realised that RE2 (the flavour of regex used by Google sheets) doesn't support the global modifier and only returns the first match (explained here). The accepted solution to that question finds a smart workaround which allowed me to solve the problem:

=join("",REGEXEXTRACT(AH2,REGEXREPLACE(AH2, "([A-Z]+(?:\s+[A-Z]+)*)","($1)")))

When considering the non-English characters, I got lost in a rabbit hole here and here and decided that I suddenly don't care that much about international characters.

user_68271970

You can use REGEXREPLACE to replace any non-capital letters with empty string and that will leave you with your desired string.

Try using this =REGEXREPLACE(A1,"[^A-ZÀ-Ö]","") and you can include additional character sets as per your needs that you want to retain.

Here is a working screenshot.

enter image description here

user_21029560

try:

=INDEX(REGEXREPLACE(A1:A, "[a-z ö-]", ))

enter image description here

user_56326290
Related