I’m working with a spreadsheet that has mixed languages in column A. I need to create a filter that only shows rows containing Japanese characters.
I attempted to use this custom filter formula:
=DETECTLANGUAGE(B:B)="ja"
However, when I apply this filter, all my data disappears and nothing shows up. I’m not sure what’s going wrong with my approach.
Has anyone successfully filtered Google Sheets data by language before? I’m specifically trying to isolate Japanese text from other languages in my dataset. Any suggestions on the correct formula or alternative methods would be really helpful.
DETECTLANGUAGE struggles with mixed content and short text strings. Your formula looks right, but double-check that column B has the text you want to filter (you mentioned column A initially). I’d try using a helper column first. Make a new column with =IF(DETECTLANGUAGE(A1)="ja","Japanese","Other") and drag it down, then filter on that. Makes it way easier to troubleshoot since you can see what it’s actually detecting. You could also try =ISERROR(DETECTLANGUAGE(A:A))=FALSE with your original formula to catch cells where detection completely fails. Empty cells or number-only cells can break the whole filter.
DETECTLANGUAGE isn’t super reliable for this. I had the same problem filtering multilingual data and found REGEX works way better for Japanese characters. Try this instead: =REGEXMATCH(A:A,"[\u3040-\u309F\u30A0-\u30FF\u4E00-\u9FAF]") - covers hiragana, katakana, and common kanji. Double-check you’re hitting the right column. Also, mixed content like numbers or English can mess with language detection. DETECTLANGUAGE often fails when there’s not enough text or mixed scripts in the cells.
DetectLanguage has been pretty buggy for me too. Try copying column A to a fresh sheet and see if the filter works there - sometimes Sheets gets confused by formatting or hidden characters. Also double-check that you’ve got Japanese text in column B since you’re filtering on B but said your data is in A.