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

There is no direct way to translate language in an Excel formula. However, you can use the Functions Translator add-in by Microsoft to do that. Fortunately, you can translate multiple cells using the Translate feature in Excel. But there is no alternative to the popular GOOGLETRANSLATE function from Google Sheets in Excel. However, you can use Excel VBA to create a User-Defined function to do that. In this article, we will highlight the ways to do all these tricks. Have a quick look!

You can use the Functions Translator add-in by Microsoft to translate the language of formulas in Excel. Follow the steps below to be able to do that.

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

First, go to the Insert tab and click on Get Add-ins as shown below.

Then, search for Functions Translator, find the Functions Translator, a Microsoft Garage project add-in, and click on the Add button as shown below.

Next, click on Continue to accept the license terms.

Step 2: Opening the Functions Translator Add-in

After that, you can access the Functions Translator from the Home tab as shown below.

Now open the Translator and Get Started.

Step 3: Setting Translation Language Settings

Then set the source_language and the target_language, and click on Start Working.

Step 4: Translating the Language of Excel Formula

Next, 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

You can use the Translate feature to translate multiple cells in Excel. Follow the steps below to be able to do that.

First, go to the Review tab and click Translate, as shown below. Alternatively, you can press ALT + SHIFT + F7 to do that.

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

Next, select the cells that you need to translate. Alternatively, you can copy and paste them onto the translator. After that, you will see the following result.

How to Translate Any Text Applying Google Translate in Excel

You can create a User-defined Function (UDF) in VBA to use the Google Translate feature in Excel. Follow the steps below to be able to do that.

First, go to Developer >> Visual Basic to open the VB editor. Alternatively, you can press ALT + F11 to do that. Then select Insert >> Module to create a new module.

Next, copy the following code and paste it onto the blank module. The credit for this code goes to XL Studio. After that, save the document as a macro-enabled workbook to avoid losing the code.

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

After that, go to Tools >> References in the VBA editor.

Then scroll down, select the Microsoft HTML Object Library checkbox, and click OK.

Now save and return to the worksheet. You need to enter the UDF in the following format.

GOOGLETRANSLATE(text, source_language, target_language)

Here, 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. Then 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 Practice Workbook

You can download the practice workbook from the download button below.

Translate Language of Formula.xlsx

Conclusion

Now you know how to translate the language of an Excel formula. Do you have any further queries or suggestions? Please let us know in the comment section below. Stay with us and keep learning.

<< Go Back to Translate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!