While Google Sheets shares many similar functions with Excel, the DETECTLANGUAGE function is another function that is only available in Google Sheets. Google Sheets has access to Google's powerful APIs, giving Google Sheets superpowers that Excel doesn't have. The DETECTLANGUAGE function uses Google Translate to determine the language of text that you specify, making it particularly useful when combined with the GOOGLETRANSLATE function in Google Sheets.
=DETECTLANGUAGE(Text)The function then returns the Google Sheets language code of the language corresponding to the text in question (language codes are listed at the bottom of this article).
The text argument can simply be text in quotes, as shown in the example below:
=DETECTLANGUAGE("Hola como estas")This function would return the letters "es" which is the language code for Spanish in Google Sheets and Google Translate.
Below are more examples of the DETECTLANGUAGE Function with Cell References.
| A | B | C | |
|---|---|---|---|
| 1 | TEXT | FORMULA | OUTPUT |
| 2 | Bonjour | =DETECTLANGUAGE(A2) | fr |
| 3 | こんにちは元気ですか | =DETECTLANGUAGE(A3) | ja |
| 4 | Olá, como vai você | =DETECTLANGUAGE(A4) | pt |
In the examples above, we see that the text in cell A2 is written in French, so if we apply the DETECTLANGUAGE function to the cell with the following formula, it returns "fr":
=DETECTLANGUAGE(A2)This is because the cell A2 contains text written in French, and "fr" is the language code for French. This is why the same formula applied to cells A3 and A4 return "ja" and "pt" which are the language codes for Japanese and Portuguese, respectively.
The DETECTLANGUAGE function can also be applied to a range of cells. According to the Google Sheets official documentation, if the range of cells contains multiple languages, the first text found will be evaluated by the function. However, in practice this is often not the first cell in the range and is relatively unreliable. It is recommended that you only use DETECTLANGUAGE on a single cell reference.
Additionally, when supplying a range of cells as the argument to the DETECTLANGUAGE function, the range must be one-dimensional, meaning that all cells must be contained in either one row or one column. Using a two-dimensional range of cells as the argument to the function will return an error as described below.
The DETECTLANGUAGE function in Google Sheets raises the #VALUE! error in two cases:
The DETECTLANGUAGE function in Google Sheets returns "und" when it is unable to determine the language of the text argument. The "und" stands for undefined, and this often happens when the text argument only contains numbers or punctuation. DETECTLANGUAGE simply returns "und" because it cannot determine which language numbers belong to. Ensure that the text argument contains text of a specific language.
The DETECTLANGUAGE function returns language codes which correspond to the languages listed below. As of May 2022, Google Translate supports language codes for 108 different languages, meaning that you can detect the language of text for 108 different possible languages in Google Sheets.
| Afrikaans | af |
| Albanian | sq |
| Amharic | am |
| Arabic | ar |
| Armenian | hy |
| Azerbaijani | az |
| Basque | eu |
| Belarusian | be |
| Bengali | bn |
| Bosnian | bs |
| Bulgarian | bg |
| Catalan | ca |
| Cebuano | ceb |
| Chinese (Simplified) | zh-cn |
| Chinese (Traditional) | zh-tw |
| Corsican | co |
| Croatian | hr |
| Czech | cs |
| Danish | da |
| Dutch | nl |
| English | en |
| Esperanto | eo |
| Estonian | et |
| Finnish | fi |
| French | fr |
| Frisian | fy |
| Galician | gl |
| Georgian | ka |
| German | de |
| Greek | el |
| Gujarati | gu |
| Haitian Creole | ht |
| Hausa | ha |
| Hawaiian | haw |
| Hebrew | he or iw |
| Hindi | hi |
| Hmong | hmn |
| Hungarian | hu |
| Icelandic | is |
| Igbo | ig |
| Indonesian | id |
| Irish | ga |
| Italian | it |
| Japanese | ja |
| Javanese | jv |
| Kannada | kn |
| Kazakh | kk |
| Khmer | km |
| Kinyarwanda | rw |
| Korean | ko |
| Kurdish | ku |
| Kyrgyz | ky |
| Lao | lo |
| Latin | la |
| Latvian | lv |
| Lithuanian | lt |
| Luxembourgish | lb |
| Macedonian | mk |
| Malagasy | mg |
| Malay | ms |
| Malayalam | ml |
| Maltese | mt |
| Maori | mi |
| Marathi | mr |
| Mongolian | mn |
| Myanmar (Burmese) | my |
| Nepali | ne |
| Norwegian | no |
| Nyanja (Chichewa) | ny |
| Odia (Oriya) | or |
| Pashto | ps |
| Persian | fa |
| Polish | pl |
| Portuguese (Portugal, Brazil) | pt |
| Punjabi | pa |
| Romanian | ro |
| Russian | ru |
| Samoan | sm |
| Scots Gaelic | gd |
| Serbian | sr |
| Sesotho | st |
| Shona | sn |
| Sindhi | sd |
| Sinhala (Sinhalese) | si |
| Slovak | sk |
| Slovenian | sl |
| Somali | so |
| Spanish | es |
| Sundanese | su |
| Swahili | sw |
| Swedish | sv |
| Tagalog (Filipino) | tl |
| Tajik | tg |
| Tamil | ta |
| Tatar | tt |
| Telugu | te |
| Thai | th |
| Turkish | tr |
| Turkmen | tk |
| Ukrainian | uk |
| Urdu | ur |
| Uyghur | ug |
| Uzbek | uz |
| Vietnamese | vi |
| Welsh | cy |
| Xhosa | xh |
| Yiddish | yi |
| Yoruba | yo |
| Zulu | zu |
To fully commit this function to memory you need to get some hands on experience. Try some practice problems in our interactive Google Sheets Tutorial now!