CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
Digital Farmer
Digital Farmer

Reputation: 2107

Remove diacritics via formula on Google Sheets (Part 2)

With the help of a StackOverflow member, we have created a way to remove diacritics from personal names, these diacritics are saved in a list and as they appear in their names, they are removed and converted to common keyboard letters.

=ARRAYFORMULA(PROPER(IFERROR(REGEXREPLACE(C2:C, TEXTJOIN("|", 1, A2:A1000),
 VLOOKUP(REGEXEXTRACT(C2:C, TEXTJOIN("|", 1, A2:A1000)), A2:B1000, 2*
 SIGN(ROW(A2:A1000)), 0)&""), C2:C)))

But ... What happened was that in one of the names instead of the formula converting the letter "í" to "i", it converted to "a"

I would like help finding out what happened as it is the first time this has occurred and I could not find the problem.

The name was:

Á. Di María

And converted to:

A. Di Maraa

The correct form would be:

A. Di Maria

Both diacritics are saved in the conversion list, so it's not the fault, there's something I'm missing but I don't know what it is.

I leave here the link to the spreadsheet, so you can make it easier to understand what happened:

https://docs.google.com/spreadsheets/d/1yfB8GskVU_ciFKuzae9XQF-pi3y6jsYtsanN46vmNOs/edit?usp=sharing

Upvotes: 2

Views: 5064

Answers (2)

david
david

Reputation: 31

another solution:

=reduce(A1; {"aá";"eé";"ií";"oó";"uú"};
lambda(string; letter; SUBSTITUTE(string; right(letter; 1); left(letter; 1))))

Upvotes: 0

player0
player0

Reputation: 1

=ARRAYFORMULA(PROPER(SUBSTITUTE(SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE(
 IFERROR(REGEXREPLACE(REGEXEXTRACT(LOWER(
 SUBSTITUTE(INDIRECT("C2:C"&COUNTA(C2:C)+1), " ", "♥")), REPT("(.)", 
        LEN(INDIRECT("C2:C"&COUNTA(C2:C)+1)))), VLOOKUP(REGEXEXTRACT(LOWER(
 SUBSTITUTE(INDIRECT("C2:C"&COUNTA(C2:C)+1), " ", "♥")), REPT("(.)", 
        LEN(INDIRECT("C2:C"&COUNTA(C2:C)+1)))), LOWER(A:B), 1, 0), VLOOKUP(REGEXEXTRACT(
 SUBSTITUTE(INDIRECT("C2:C"&COUNTA(C2:C)+1), " ", "♥"), REPT("(.)", 
        LEN(INDIRECT("C2:C"&COUNTA(C2:C)+1)))), LOWER(A:B), 2, 0)), REGEXEXTRACT(
 SUBSTITUTE(INDIRECT("C2:C"&COUNTA(C2:C)+1), " ", "♥"), REPT("(.)", 
        LEN(INDIRECT("C2:C"&COUNTA(C2:C)+1)))))),,999^99)), " ", ), "♥", " ")))

0

Upvotes: 1

Related Questions