How to extract words entirely written in uppercase with accents (Diacritics) with a Google Sheet REGEXEXTRACT formula?

user_14836487 0
Viewed 414

Ok,

it looks simple but when the words start or finish with an accents it is the mess. I've looked on Stack Overflow and others and haven't really found a way to solve this problem. I would like, to be able with a Google sheet formula, to extract from a cell, words only built with the ASCII characters that follow: A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,À,Á,Â,Ã,Ä,Å,Æ,Ç,È,É,Ê,Ë,Ì,Í,Î,Ï,Ð,Ñ,Ò,Ó,Ô,Õ,Ö,Ø,Ù,Ú,Û,Ü,Ý

For example with "Éléonorä-Camilliâ ÀLËMMNIÖ DE SANTORINÕ" or "ÀLËMMNIÖ DE SANTORINÕ Éléonorä Camilliâ" the result has to be the same "ÀLËMMNIÖ DE SANTORINÕ"

This formula works when no accent all: =REGEXEXTRACT(A2;"\b[A-Z]+(?:\s+[A-Z]+)*\b")

These formula work sometimes when the names are easy.

=REGEXEXTRACT(A2;"\b[A-Ý]+(?:\s+[A-Ý]+)*\b")

=REGEXEXTRACT(A2;"\B[A-Ý]+(?:\S+[A-Ý]+)*\B")

Can anybody help me or give me some hint?

google-sheets diacritics ascii regex
4 Answers

It seems your expected matches are simply between whitespace or start/end of string. If you add a space before and after the cell value, you may simply extract all the chunks of whitespace-separated uppercase letter words between whitespaces, and the formula will boil down to

=REGEXEXTRACT(" " & A2 & " "; "\s([A-ZÀ-ÖØ-Ý]+(?:\s+[A-ZÀ-ÖØ-Ý]+)*)\s")

See the Google sheets demo:

enter image description here

Regex details:

  • \s - a whitespace
  • ([A-ZÀ-ÖØ-Ý]+(?:\s+[A-ZÀ-ÖØ-Ý]+)*) - Group 1 (the actual value returned by REGEXEXTRACT): one or more uppercase letters from the specified ranges followed with zero or more repetitions of one or more whitespace and then one or more uppercase letters
  • \s - a whitespace.

You may use an ARRAYFORMULA, as well:

=ARRAYFORMULA(IFERROR(REGEXEXTRACT(" " & A:A & " ", "\s([A-ZÀ-ÖØ-Ý]+(?:\s+[A-ZÀ-ÖØ-Ý]+)*)\s"),""))
user_38329700

Supposing your sample name were in A2, this should work:

=TRIM(REGEXEXTRACT(A2&" ","([A-ZÀ-Ý ]+)\s"))

By appending a space to the end of the string first, we can then look for the [uppercase letter set or space] in any number up ending with a space. This rules out strings like "Éléonorä" and "Camilliâ" because those uppercase letters are not followed by a space.

Put a different way, the rule here says, "Grab as many uppercase letters or spaces in this set as possible, as long as you still have a space left over at the end." And since we appended a space to the end of the entire string, we can catch such groupings anywhere in the modified string.

user_139032830

use:

=ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(""<>
 IFERROR(REGEXEXTRACT(SPLIT(A1:A, " "), "["&TEXTJOIN("", 1, 
 UNIQUE(QUERY({UPPER(CHAR(ROW(65:1500))), LOWER(CHAR(ROW(65:1500)))}, 
 "select Col2 where Col1<>Col2")))&"]+")),,IFERROR(SPLIT(A1:A, " ")))),,9^9))))

enter image description here

or 10 characters shorter:

=INDEX(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(""<>
 IFERROR(REGEXEXTRACT(SPLIT(A:A; " "); "["&JOIN(; 
 UNIQUE(LOWER(QUERY(CHAR(ROUNDUP(SEQUENCE(1500; 2; 65)/2)); 
 "select Col1 where lower(Col1)<>upper(Col2)"))))&"]+"));;
 IFERROR(SPLIT(A:A; " "))));;9^9))))

works with all Europe-based alphabets and captures all diacritics out there. it can differentiate between:

LOWER

enter image description here

and

UPPER

enter image description here

user_56326290

Try this- backslashing the non A-Z characters.

[A-Z\À\Á\Â\Ã\Ä\Å\Æ\Ç\È\É\Ê\Ë\Ì\Í\Î\Ï\Ð\Ñ\Ò\Ó\Ô\Õ\Ö\Ø\Ù\Ú\Û\Ü\Ý]

If that fails you can encode each one of those letters like below: Look up for characters: https://www.w3schools.com/charsets/ref_utf_latin1_supplement.asp

[A-Z\u00C0\u00C1... and so on...]
user_48314270
Related