I have a Spreadsheet with content in a foreign language. How can I read that? It’s possible. You can detect and translate content in Google Sheets using its built-in functions.
Open your Spreadsheet in Google Sheets and there use the functions DETECTLANGUAGE and GOOGLETRANSLATE in a combined form.
These are two individual Google Sheet functions to help you translate content within Spreadsheet.
Then why are you using or suggest us to use these functions in a combined form?
The answer is simple. Most of the time it may be difficult for you to find the foreign language that you want to translate. Even if you could find the language you must know the language code.
The DETECTLANGUAGE function finds the language codes for you and that works within the GOOGLETRANSLATE function.
I don’t have posted any individual tutorials for the above two functions. Because it’s already floating on the web. But for the example purpose, I am detailing it below.
How to use the DETECTLANGUAGE Function in Google Sheets
Syntax:
DETECTLANGUAGE(text_or_range)
Examples of Google Sheets DETECTLANGUAGE Function:
In the above examples, I have typed the below formula in B2 then dragged to down.
=DETECTLANGUAGE(A1)
I have heard that I can use the above formula in a range. Is that true?
Of course. You can use the formula as below. But it won’t return an array result.
=DETECTLANGUAGE(A1:A5)
If the range specified, that also one dimensional, contains multiple languages, the first text string found will be evaluated.
How to use the GOOGLETRANSLATE Function in Google Sheets
I know our topic is how to detect and translate content in Google Sheets. You have already seen how to detect a foreign language in Google Sheets.
Now see how to Translate that. The combination formula follows after that.
Syntax:
GOOGLETRANSLATE(text, [source_language, target_language])
Examples of Sheets GOOGLETRANSLATE Function:
The GOOGLETRANSLATE function in Google Sheets is not an array formula. So when you want to translate texts in multiple cells, enter the formula in one cell and drag to down.
Here my formula is in cell D1 as below that dragged to down.
=GOOGLETRANSLATE(A1,B1,C1)
I have the text to translate in cell A1, the source language in B1 (in B1 I have the DETECTLANGUAGE formula) and the target language in C1.
The target language code is ‘en’ that is English. Now I am going to tell you how to use the DETECTLANGUAGE function within GOOGLETRANSLATE to replace the source language.
Detect and Translate Content in Google Sheets Using Combined Functions
Here is the combo that detects and translate content in Google Sheets.
Formula:
=GOOGLETRANSLATE(A1,DETECTLANGUAGE(A1),"en")
How to Apply and Result:
With the help of the DETECTLANGUAGE function, the GOOGLETRANSLATE formula automatically detects and translate the contents in the given range.
That means without knowing the language codes, you can translate foreign language cell contents in Google Sheets.
Can you provide me with the language codes for my reference?
Language Codes to Use in GOOGLETRANSLATE Function in Google Sheets
I don’t have the language codes to provide you. But I found a reliable source link here. I am using that link in the IMPORTHTML function in Google Sheets below to import the language codes.
=importhtml("https://developers.google.com/admin-sdk/directory/v1/languages","table",1)
Use this formula in cell A1 in a Blank Google Sheet. Please do note that I didn’t test the codes myself.
Here are the language codes.
Follow the above tips to detect and translate content in Google Sheets.
