Md. Shamim Reza
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!
Table of Contents
hide
Download Practice Workbook
You can download the practice workbook from the download button below.
4 Steps to Translate Language in Excel Formula
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: Insert Functions Translator Add-in
- 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: Open 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: Set Translation Language Settings
- Then set the source_language and the target_language, and click on Start Working.
Step 4: Translate 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.
Read More: How to Translate an Excel File from Japanese to English
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. You can find the language codes from this link.
- 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 Windows10 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.
Read More: How to Translate Excel File to English (2 Easy Ways)
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.
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. You can also visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.
