Translate Function in Google Sheets

Translate Function in Google Sheets
Reading Time: 4 minutes

What is the Translate Function in Google Sheets?

The translate function in Google Sheets translates text inside a sheet into different languages. 

Example of Translate function in Google Sheets
Figure 1: Example of Translate function in Google Sheets

Why use the Translate Function in Google Sheets?

Google Sheets is used worldwide. Let’s say you are working in a multinational firm with offices all around the globe. You want to share Google Sheet files with a person from another country. Therefore, having a function which translates text to different languages is very useful.

How to use the Translate Function in Google Sheets?

Syntax

GOOGLETRANSLATE(text, [source_language, target_language])

  • Text – The text to translate.
    • The value for text must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
  • source_language – [ OPTIONAL – “auto” by default ] – The two-letter language code of the source language, e.g. “en” for English or “ko” for Korean, or “auto” to auto-detect the language.
    • If source_language is omitted, target_language must also be omitted.
  • target_language – [ OPTIONAL – system language by default ] – The two-letter language code of the target language, e.g. “en” for English or “ja” for Japanese.

Sample Examples

GOOGLETRANSLATE(“My name is Cristiano Ronaldo”, “en”, “es”):- Here, The text to be translated is Hello World. The text is translated from English to Spanish. Here, the value of the text is enclosed in quotation marks.

GOOGLETRANSLATE(A2, B2, C2):- Here, the cell containing the text is referenced.

Steps to use the Translate Function in Google Sheets

Step 1: Open Google Sheets

  • Open the Sheet of interest.
  • Navigate to the cells you want to translate.

Step 2: Use the GOOGLETRANSLATE Formula

  • Let’s say you want to translate a sentence into German.
  • Enter the formula:

=GOOGLETRANSLATE(A2,”en”,”de”)

 Sentences of interest
Figure 2: Sentences of interest
  • This will translate the text to German. Drag down the selection to the last cell of the column to apply the formula to all cells.
English sentences translated to German
Figure 3: English sentences translated to German

Some of the examples of most frequently used languages and their symbol is:

LanguageShort Form
Englishen
Germande
Frenchfr
Italianit
Hindihi
Spanishes

GOOGLETRANSLATE supports over 60 languages. So if you want to translate text into any other language, check out this link to learn the short forms of your desired language.

Using Auto as a Language Argument

There might be times when you are unaware of the language of the text. What if you want to convert that text into a language of your choice? What will you enter in the source language parameter?

To solve this problem, you can use “Auto” as the source language and convert it to text in your language.

You have a collection of sentences in different languages, and you want to translate them into English. Here’s how you can go about it:

Step 1: Use the GOOGLETRANSLATE function with Auto as the source language

  • Let’s say you have a column of sentences in different languages, as written in the column “Sentences in Different Languages”:
Sentences in different languages
Figure 4: Sentences in different languages
  • You want to change all of them to English.
  • Use formula:

=GOOGLETRANSLATE(A10,”auto”,”en”)

  • This will convert the first sentence into English.

Step 2: Drag down the selection to apply to all the cells.

  • Now click on the bottom right corner of the cell and drag the selection down to the last cell of the corresponding row.
  • You will see that all the sentences are translated.
All sentences translated to English
Figure 5: All sentences translated to English

Conclusion

You can use the translate function in Google Sheets to translate text from different languages to the desired language. Using the GOOGLETRANSLATE function is very easy and translates text very fast.

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

Filter Data by Color in Google Sheets: In this article, we would have a look at how to Filter Data by Color in Google Sheets.

Google Sheets: How to Use Pivot Tables: Learning how to create and use Pivot Tables for analysing data in Google Sheets.

Conditional Formatting in Google Sheets: Get started with Conditional Formatting in Google Sheets