I’m having trouble setting up a custom filter in Google Sheets to show only rows where cells contain Japanese characters. I put this formula in the custom formula field:
=DETECTLANGUAGE(B:B)="ja"
But when I apply the filter, it hides everything and no rows show up at all. I expected it to display only the cells with Japanese text but instead I get a completely empty sheet. Has anyone else run into this issue? I’m not sure if my syntax is wrong or if there’s a different approach I should be using. The column definitely has some Japanese text mixed with English entries, so there should be results showing up. Any suggestions on how to fix this filtering problem?
yep, detectlanguage is super flaky! a helper column with true/false values make things so much smoother for filters. way less headache than trying to use the function directly, trust me.
DETECTLANGUAGE is pretty unreliable in filter conditions - it checks each cell separately and gets confused with mixed content. I’ve run into this same issue with multilingual data. Skip the language detection and use REGEX instead: =REGEXMATCH(B2,"[\u3040-\u309F\u30A0-\u30FF\u4E00-\u9FAF]") but make sure you’re referencing individual rows, not entire columns. Or create a helper column with your detection formula first, then filter off that. The two-step approach works way better than cramming everything into one filter formula.
DETECTLANGUAGE gets wonky when you use it in filter conditions - it handles entire columns differently than single cells. I ran into this same issue last year with multilingual spreadsheets. The function gives inconsistent results in filters, which is why you’re getting zero results. Skip DETECTLANGUAGE for your custom filter and use a character-based approach instead - check for specific Japanese unicode ranges. It’s way more reliable since you’re not depending on Google’s language detection, which gets unpredictable with mixed content or short text.
same issue here! detectlanguage doesn’t work well with filters. regex is way better for this - try =REGEXMATCH(B:B,"[\u3040-\u309F\u30A0-\u30FF\u4E00-\u9FAF]") to catch japanese characters.