How to display, using a filter, only cells that contain Japanese in Google Sheets?

Question

I tried this formula in the custom formula box:

=DETECTLANGUAGE(A:A)=ja

But it doesn't work. All cells are hidden. What am I doing wrong?

Answer 1

You can use query's regex(preg):

=QUERY(A:A,"where A matches '.*[\p{Hiragana}\p{Katakana}\p{Han}]+.*'")

• .* Match any character unlimited number of times
• []+ Match any character inside between one and unlimited number of times
• \p{...}Unicode class for Han,Hiragana and Katakana scripts
• Note that the regex should match the full cell. The way it's written now, it'll return the cell even if one of Han,Hiragana and Katakana character is found.

Answer 2

note that DETECTLANGUAGE does not work with array/range so only:

=IF(DETECTLANGUAGE(A1)="ja", "Japanese", )

---

but you could use a script:

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

---

=ARRAYFORMULA(FILTER(A1:A, IF(LEN(A1:A)=LEN(NIPPON(A1:A)), LEN(A1:A), )>0))

---

example of using DETECTLANGUAGE in an array for English detection:

=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), )), "-")