2

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.

  • Welcome. It's not clear what you are asking. What is the problem with the first formula? Also the post is too broad for this site as it include two questions. – Rubén Jan 29 at 5:29
  • Thank you! My apologies I didn't explain clearly. The first formula only worked when extending manually, and didn't extend as an ArrayFormula does. – Jon Jan 29 at 18:30
1

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

  • Hello! Thank you for your suggestion. After implementing the script you suggested and using your formula, I'm getting this error: "Internal error executing the custom function." perhaps due to the English fields needing to be the same length? – Jon Jan 29 at 19:04
  • I was afraid of that; the issue with custom functions is that if they take longer than 30 seconds to run, they fail. So if the data set you are working with is too great, you end up with an error. The biggest time cruncher here is that the Language service is a call and thus takes longer. You might have to consider reworking how your source data is tabulated as I am out of ideas :( – sageco Jan 29 at 23:37
  • No problem! I ended up doing a temporary solution by using CONCATENATE on the entire range to filter out specific characters before counting them. – Jon Jan 31 at 0:29
0

=IFERROR(ARRAYFORMULA(IF(LEN(A5:A), 
 IF({DETECTLANGUAGE(A5)="en";
     DETECTLANGUAGE(A6)="en";
     DETECTLANGUAGE(A7)="en";
     DETECTLANGUAGE(A8)="en";
     DETECTLANGUAGE(A9)="en";
     DETECTLANGUAGE(A10)="en"}, 
 LEN(A5:A), 0), )), "-")

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.