TRANSLATE Function

The TRANSLATE function translates text from one language to another in Excel. To perform a translation, TRANSLATE requires the text to translate and language codes to indicate the source and target language. The syntax for TRANSLATE looks like this:

=TRANSLATE(text,[source_language],[target_language])

Typically, the text is supplied as a cell reference like A1, but it can also be hardcoded as a string like "apple". While TRANSLATE takes three arguments, only the first argument is required. The other two arguments have the following default behaviors:

• source_language - if not provided, the source language will be detected automatically by inspecting the text. Essentially, this is the same result you see with the DETECTLANGUAGE function, but you will not see what language TRANSLATE has selected. It is recommended to specify the source language when possible.
• target _language - if not provided, the target language will default to the system setting of the user's computer. This might be convenient in some cases, but remember that you will not know this setting if you share a file with others.

The TRANSLATE function uses Microsoft Translation Services and requires an internet connection.

Table of Contents

• Basic Example
• Example: Translate text into several languages
• Example: Translate instructions dynamically
• Codes for common languages
• Notes

Basic Example

To use the TRANSLATE function, provide the text to translate, the source language language as a language code, and the desired target language as a language code. For example, to translate the word "apple" into French ("fr"), Italian ("it"), German ("de"), and Spanish ("es"), you can use the TRANSLATE function like this:

=TRANSLATE("apple","en","fr")  //  returns "pomme"
=TRANSLATE("apple","en","it")  //  returns "mela"
=TRANSLATE("apple","en","de")  //  returns "Apfel"
=TRANSLATE("apple","en","es")  //  returns "manzana"

Note the source and target languages are specified with codes: English is "en", Italian is "it", German is "de", and Spanish is "es". The TRANSLATE function supports over 100 languages. See below for a list of codes for some common languages. 

Example: Translate text into several languages

In this example, the goal is to translate the text entered in cell C5 into the 9 languages in the table below, using the codes in the range B8:B16 to determine the target languages. The formula in cell C8, copied down, looks like this:

=TRANSLATE($C$5,$B$5,B8)

For this problem, the TRANSLATE function is configured as follows:

• text - provided as $C$5, locked to prevent changes when copying
• source_language - provided as $B$5, locked to prevent changes when copying
• target_language - provided as B8, relative to allow the reference to update when copying

As the formula is copied down column B, TRANSLATE returns a translation for the text in cell C5 using the language codes in column B to determine a target language.

Note that the TRANSLATE function is dynamic. If the text in cell B5 is changed, TRANSLATE will generate new translations. When a target language code in the range B8:B16 is changed, TRANSLATE will return a translation for the new target language.

Example: Translate instructions dynamically

In this example, the goal is to translate instructions dynamically based on the language selected in a dropdown list, as shown in the workbook below. Here, the instructions are located in the range B4:B11, and the translation appears in E4:F11. The dropdown list appears in cell F2. The formula in cell E4 looks like this:

=TRANSLATE(B4,"en",target)

Where "target" is a named range on another sheet, as explained below. Note that we are translating the table headers in addition to the instructions, so we start with cell B4. As the formula is copied down and across the table, it returns a translation for each cell using the language in cell F2 as the "target" language:

Although we now have a target language in F2, the TRANSLATE function requires a language code. To convert the language name into the correct code, we have set up an Excel Table on a separate worksheet, as shown below. The table is named "languages" and performs two important functions:

1. The first column in the table supplies values to the dropdown menu in cell F2 with Data Validation.
2. The XLOOKUP formula in F2 uses the table to find the correct language code for the selected language.

The dropdown menu is created using a Data Validation list that points to the first column of the "languages" table in the range B5:B24 on Sheet3. For mysterious reasons, Excel will not display the table name in this case, but it will update the Source range if the table changes:

Below is the XLOOKUP formula in F5:

=XLOOKUP(D5,languages[Language],languages[Code])

The inputs to XLOOKUP are as follows:

• lookup_value - the named range "lang" (=Sheet2!F2)
• lookup_array - languages[Language]
• return_array - languages[Code]

Note that F5 is the named range "target", which provides a value for target_language in the TRANSLATE function. To recap, here is how this example works:

• There are two named ranges: "lang" is cell F2 on Sheet2, which holds the result of the dropdown selection, and "target" is cell F5 on Sheet3 which holds the language code returned by XLOOKUP.
• The Excel Table named "languages" is on Sheet3. This table contains the languages available to select in the dropdown in the first column (Language) and the corresponding language code in the second column (Code).
• When a user selects a language from the dropdown menu, the value for "lang" (F2) is set, and the XLOOKUP formula in F5 returns the corresponding language code, which sets the value for "target."
• The TRANSLATE formulas in the main worksheet use the target language code to perform a translation.
• When the user selects a different language, the process repeats, and a new translation is automatically retrieved. 

Excel Tables have a nice feature that we use in this example: If you add more languages to the table, the dropdown menu will automatically update. For more information about tables, see our Excel Table guide.

Codes for common languages

The below shows language codes for some common languages that can be used with the TRANSLATE function. Note that some languages, like Portuguese, have more than one variant. For example, the code "pt" specifies Portuguese for Brazil, while "pt-pt" specifies Portuguese for Portugal.

The TRANSLATE function supports over 130 languages. You can find the full list of supported languages here.   

Notes

• TRANSLATE always returns text, even if the text is numeric.
• If the text is an empty string (""), TRANSLATE returns an empty string.
• If a language code is not valid, TRANSLATE returns a #VALUE! error.
• If the internet is not available, you may see a #CONNECT! error.