Google-sheets – ARRAYFORMULA with IF, DETECTLANGUAGE and LEN

formulasgoogle sheetsgoogle-apps-scriptgoogle-sheets-arrayformulagoogle-sheets-custom-function

I'm trying to make an array formula to detect the number of English characters in a range.

When I use the following for individual cells:

=IF(DETECTLANGUAGE(ARRAYFORMULA(A5:A))="en",LEN(ARRAYFORMULA(A5:A)),0)

I get the correct values, but it only works when I extend the cells manually (but I want it to work as an array formula that extends automatically).

=ArrayFormula(IF(DETECTLANGUAGE(A5:A)="en",LEN(A5:A),0))

The above only checks if the first cell, A5, is English, then counts all the characters in the other cells without first checking if each cell is in English.

I'd also be willing to simply count all English chars in an entire range, but I ran into a similar problem with checking the language of only the first cell in the range.

Best Answer

Hmm, based on the documentation for Detectlanguage, it doesn't look like you can feed it a range.

The best it seems you can do is define it as an array formula and check each line you wish to check, but that won't really work for an arbitrarily large array.

An idea I had was to use the app's service to translate everything to English and then compare lengths.

ie, you create the below custom function:

function Lang_Check(input) {
     var output = [];
     for (i = 0 ; i < input.length; i++){
    try {
    output[i] = LanguageApp.translate(input[i], '', 'en');
    }
    catch(err) {
      output[i] = err.message;
      }
      }   return output; }

Then, you run it as in the below formula:

=ArrayFormula(IF(LEN(A2:A) = LEN(Lang_Check(A2:A)),Len(A2:A),))

In theory, only the English language cells remain the same length, but this is just an assumption and might not always be true.

enter image description here

Related Topic