How to Translate Language in Excel Formula (with Easy Steps)

You can use the Functions Translator add-in by Microsoft to translate the language of formulas in Excel.

Step 1 – Inserting the Functions Translator Add-in to Translate Language in Excel

Go to the Insert tab and click on Get Add-ins as shown below.

Search for Functions Translator.
You’ll get the Functions Translator, a Microsoft Garage project add-in.
Click on the Add button on the result.

Click on Continue to accept the license terms.

Step 2 – Opening the Functions Translator Add-in

You can access the Functions Translator from the Home tab as shown below.

Open the Translator and Get Started.

Step 3 – Setting the Translation Language Settings

Set the source language and the target language, then click on Start Working.

Step 4 – Translating the Language of Excel Formulas

Go to the Translator tab, enter the formula, and click on the Translation Direction arrow to see the result.

Note: You can easily change the language settings by clicking on the settings icon.

How to Translate Multiple Cells in Excel

Go to the Review tab and click Translate. Alternatively, you can press Alt + Shift + F7.

The Translator task pane will open. You can change the source and target language using the dropdowns.

Select the cells that you need to translate. Alternatively, you can copy and paste them onto the translator.

How to Translate Any Text with Google Translate in Excel

Go to Developer and select Visual Basic to open the VBA editor. Alternatively, you can press Alt + F11 to do that.
Select Insert and choose Module to create a new module.

Copy the following code and paste it onto the blank module.

Function GOOGLETRANSLATE(text As String, source_language As String, target_language As String) As String
Dim URL As String
URL = "https://translate.google.com/m?sl=" & source_language &"&tl="& target_language &"&hl=en&ie=UTF-8&q=" & text
Dim XMLHTTPS As Object
Set XMLHTTPS = CreateObject("MSXML2.ServerXMLHTTP")
XMLHTTPS.Open "GET", URL, False
XMLHTTPS.setrequestheader "User-Agent", "Mozilla/5.0 (compatible;MSIE 6.0; WindowsNT 10.0))"
XMLHTTPS.send ""

Dim HTML As Object
Set HTML = CreateObject("HTMLFile")
With HTML
  .Open
  .write XMLHTTPS.responseText
  .Close
End With

Dim HTMLDc As HTMLDocument
Set HTMLDc = HTML

Dim Class As Object
Set Class = HTMLDc.getElementsByClassName("result-container")(0)
If Not Class Is Nothing Then
GOOGLETRANSLATE = Class.innerText
End If

Set Class = Nothing
Set HTML = Nothing
Set XMLHTTPS = Nothing

End Function

Save the document as a macro-enabled workbook to avoid losing the code.
Go to Tools and choose References in the VBA editor.

Select the Microsoft HTML Object Library checkbox and click OK.

Save and return to the worksheet.
Enter the UDF in the following format.

GOOGLETRANSLATE(text, source_language, target_language)

The text argument refers to the value you need to translate, the source_language argument asks for the code of the language of the original text, and the target_language argument asks for the code of the language to which you need to translate the text.

Assume you have entered the arguments in cells B5:D5. Apply the following formula in cell E5 to get the desired result.

=GOOGLETRANSLATE(B5,C5,D5)

Note: You need an active internet connection for this formula to work.

VBA Code Explanation:

Function GOOGLETRANSLATE(text As String, source_language As String, target_language As String) As String
Enter a custom function name and add 3 parameters to it.
Dim URL As String
Create a variable to store a dynamic URL (uniform resource locator).
URL = “https://translate.google.com/m?sl=” & source_language &”&tl=”& target_language &”&hl=en&ie=UTF-8&q=” & text
Assign the dynamic URL to the variable.
Dim XMLHTTPS As Object
Create an object variable. You can choose any name for it.
Set XMLHTTPS = CreateObject(“MSXML2.ServerXMLHTTP”)
Create an object to do a web-server API (application programming interface) call.
XMLHTTPS.Open “GET”, URL, False
Open the web server API URL.
XMLHTTPS.setrequestheader “User-Agent”, “Mozilla/5.0 (compatible;MSIE 6.0; WindowsNT 10.0))”
Set the headers to do the web-server API call. WindowsNT 10.0 has been used for the Windows 10 operating system. This may need to be changed based on the operating system.
XMLHTTPS.send “”
Send the GET method to the web server. It will do a web-server API call and get a response text in return.
Dim HTML As Object
Add an object variable to create an HTML file using that response text.
Set HTML = CreateObject(“HTMLFile”)
Create an object for the HTML file.
With HTML
.Open
.write XMLHTTPS.responseText
.Close
End With
Open the HTML file. Add the response text to the HTML file. Close the HTML file.
Dim HTMLDc As HTMLDocument
Create an HTML document variable to read the response text from the HTML file. You need to add the Microsoft HTML Object Library as the reference to do that. You can do that from Tools >> References in the VB editor.
Set HTMLDc = ObjHTML
Store the HTML file in the HTML document library.
Dim Class As Object
Create an object variable to read the translated text from the HTML file using the HTML web element.
Set Class = HTMLDc.getElementsByClassName(“result-container”)(0)
Read the class with the required name from the HTML file.
If Not Class Is Nothing Then
GOOGLETRANSLATE = Class.innerText
End If
Read the text from the class if the HTML file can read the class name. Then add the text from the class to the custom function.
Set Class = Nothing
Set HTML = Nothing
Set XMLHTTPS = Nothing
Release the memory.
End Function
Exit from the function.

Things to Remember

There is no direct way to translate language using a formula in Excel, unlike the GOOGLETRASLATE function in Google Sheets.
The UDF is designed to work on Windows 10. You may need to modify the code for other operating systems.

Download the Practice Workbook

Translate Language of Formula.xlsx

<< Go Back to Translate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Emrah Jun 7, 2024 at 1:34 PM

Thank you very much. It really is an incredible function. Would it be possible to adapt a similar function to DeepL?
- Reply
  Shamima Sultana Jun 9, 2024 at 10:58 AM
  
  Hello Emrah,
  
  You are most welcome. To adapt DeepL you may use third party Add-ins, Power Query and VBA code. But VBA code will be more complex as you need to use JSON converter.
  
  You can use the Add-ins “Text Translator for Excel”
  
  Using Power Query with DeepL API
  
  Use the Power Query to call the DeepL API directly from Excel.
  Follow the steps given below:
  
  Step 1: Get Your API Key from DeepL
  Sign up on the DeepL website and get your API key.
  Step 2: Open Excel and Launch Power Query
  Open Data tab > from Other Sources > select Web.
  
  Step 3: Configure the API Request
  In the Web dialog box, enter the DeepL API URL with your query parameters,
  Insert YOUR_API_KEY with your actual API key and modify the text and target_lang parameters as needed.
  Then click OK.
  
  Step 4: Transform the Data
  Power Query will open a new window with the API response.
  Use the available transformation tools to parse the JSON response and extract the translated text.
  Finally, Close & Load to import the data back into Excel.
  
  Regards
  ExcelDemy