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!


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.

insert excel add-in

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

search for Functions Translator add-in

  • Next, click on Continue to accept the license terms.

accept 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.

go to Home > Translator

  • Now open the Translator and Get Started.

get started to translate language in excel formula


Step 3: Set Translation Language Settings

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

set the source and target language


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.

translate language in excel formula

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.

go to Review > Translate to translate language in excel

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

translator task pane

  • 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.

translate language of multiple cells


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.

insert new module in excel vba

  • 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

VBA code for GOOGLETRANSLATE function in excel

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

add reference in VBA

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

Microsoft Object Library

  • 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)

translate language using GOOGLETRANSLATE function in Excel

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.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo