How can I normalize / asciify Unicode characters in Google Sheets?

Question

I'm trying to write a formula for Google Sheets which will convert Unicode characters with diacritics to their plain ASCII equivalents.

I see that Google uses RE2 in its "REGEXREPLACE" function. And I see that RE2 offers Unicode character classes.

I tried to write a formula (similar to this one):

REGEXREPLACE("público","(\pL)\pM*","$1")

But Sheets produces the following error:

Function REGEXREPLACE parameter 2 value "\pL" is not a valid regular expression.

I suppose I could write a formula consisting of a long set of nested SUBSTITUTE functions (Like this one), but that seems pretty awful.

Can any offer a suggestion for a better way to normalize Unicode letters with diacritical/accent marks in a Google Sheets formula?

score 8 · Accepted Answer · edited May 23 '17 at 12:22

8

[[:^alpha:]] (negated ASCII character class) works fine for REGEXEXTRACT formula.

But =REGEXREPLACE("público","([[:alpha:]])[[:^alpha:]]","$1") gives "pblic" as a result. So, I guess, formula doesn't know what exact ASCII character must replace "ú".

Workaround

Let's take the word públicē; we need to replace two symbols in it. Put this word in cell A1, and this formula in cell B1:

=JOIN("",ArrayFormula(IFERROR(VLOOKUP(SPLIT(REGEXREPLACE(A1,"(.)","$1-"),"-"),D:E,2,0),SPLIT(REGEXREPLACE(A1,"(.)","$1-"),"-"))))

And then make directory of replacements in range D:E:

    D    E  
1   ú   u
2   ē   e
3  ...  ...

This formula is still ugly, but more useful because you can control your directory by adding more characters to the table.

Or use Java Script

Also found a good solution, which works in google sheets.

edited May 23 '17 at 12:22

Community

1
1

answered Feb 26 '16 at 12:22

Max Makhrov

17,309
5
55
81

After two years, I suppose I need to accept this solution, but honestly these workarounds are all still pretty ugly. Come on, Google, fix this! – Kirkman14 Feb 15 '18 at 18:08
Hi, @Kirkman14.[This text functions](https://support.google.com/docs/table/25273?visit_id=1-636546276795993789-3021020026&hl=en&rd=2) is all we have so far. The better solution would be writing a custom function or [reporting a problem](https://stackoverflow.com/a/12106694/5372400) on google forum. – Max Makhrov Feb 19 '18 at 09:09

score 3 · Answer 2 · edited Jan 25 '19 at 14:28

This did it for me in Google Sheets, Google Apps Scripts, GAS

function normalizetext(text) {
    var weird = 'öüóőúéáàűíÖÜÓŐÚÉÁÀŰÍçÇ!@£$%^&*()_+?/*."';
    var normalized = 'ouooueaauiOUOOUEAAUIcC                 ';
    var idoff = -1,new_text = '';
    var lentext = text.toString().length -1

    for (i = 0; i <= lentext; i++) {
        idoff = weird.search(text.charAt(i));
        if (idoff == -1) {
            new_text = new_text + text.charAt(i);
        } else {
           new_text = new_text + normalized.charAt(idoff);
        }
    }

    return new_text;
}

Doomd · Answer 3 · 2020-07-26T21:47:49.847

This answer doesn't require a Google App Script, and it's still fast, and relatively simple. It builds on Max's answer by providing a full lookup table, and it also allows for case-sensitive transliteration (normally VLOOKUP is NOT case-sensitive).

Here is a link to the Google Spreadsheet if you want to jump right into it. If you want to use your own sheet, you'll need to copy the TRANS_TABLE sheet into your Spreadsheet.

In the code snippet below, the source cell is A2, so you'd place this formula in any column on row 2. Using REGEXREPLACE AND SPLIT, we split apart the string in A2 into an array of characters, then USING ARRAYFORMULA, we do the following to EACH character in the array: First, the character is converted to its 'decimal' CODE equivalent, then matched against a table on the TRANS_TABLE sheet by that number, then using VLOOKUP, a character X number of columns over (the index value provided) on the TRANS_TABLE sheet (in this case, the 3rd column over) is returned. When all characters in the array have been transliterated, we finally JOIN the array of characters back into a single string. I provided examples with named ranges as well.

=iferror(
join(
  "",
  ARRAYFORMULA(
    vlookup(
      code(split(REGEXREPLACE($A2,"(.)", "$1;"),";",TRUE)),
      TRANS_TABLE!$A$5:$F,3
    )
  )
)
,)

You'll note on the TRANS_TABLE sheet I made, I created 4 different transliteration columns, which makes it easy to have a column for each of your transliteration needs. To reference the column, just use a different index number in the VLOOKUP. Each column is simply a replacement character column. In some cases, you don't want any conversion made (A -> A or 3 -> 3), so you just copy the same character from the source Glyph column. Where you DO want to convert characters, you type in whatever character you want replaced (ñ -> n etc). If you want a character removed altogether, you leave the cell blank (? -> ''). You can see examples of the transliteration output on the data sheet in which I created 4 different transliteration columns (A-D) referencing each of the Transliteration tables from the TRANS_TABLE sheet for different use case scenarios.

I hope this finally answers your question in a fashion that isn't so "ugly." Cheers.

Worked really well. One suggestion, in the TRANS_TABLE sheet, if you could update the formulas there to use TRANS_TABLE!TRANS_TABLE and TRANS_TABLE!TRANS_A, then the formulas would work if someone copies your sheet to their sheet, then copies the cell formula to their cell. — Thomas Vander Stichele, Jan 31 '21 at 03:22
I see how that's confusing...I used "Named Ranges" in the first four columns. Instead, I split the named range version into a separate sheet. — Doomd, Feb 01 '21 at 18:31

How can I normalize / asciify Unicode characters in Google Sheets?

3 Answers3

Linked