You can build a translation function in Excel that enables users to translate text directly in the Excel workbook. It translates texts into various languages, enhancing productivity and eliminating the need for external tools In this article, we will create a smart translation function in Excel using Google’s Translate API and website.
Using Google’s Translate API to Build Smart Translation Function
Step 1: Get the Google Cloud Translation API
- Go to the Google Cloud Console.
- Create a new project or use an existing one.
- Navigate to APIs & Services >> select Library.
- Search for Cloud Translation API and enable it.
- Generate an API key:
- Go to APIs & Services >> select Credentials.
- Click Create Credentials and choose API Key.
- Copy the key and store it securely.
Step 2: Insert the VBA Code
Now we’ll create a User-Defined Function (UDF) in VBA to interact with the Google Translate API.
- Go to the Developer tab >> select Visual Basic.
- In the VBA editor, go to Insert tab >> select Module.
- Insert the following VBA code in the Module to create a user-defined function.
VBA Code:
Option Explicit
' Google Cloud Translation API Key (replace with your actual key)
Const API_KEY As String = "YOUR_GOOGLE_CLOUD_API_KEY"
' Translate Text Function
Function TranslateText(inputText As String, targetLang As String, Optional sourceLang As String = "auto") As String
Dim url As String
Dim xml As Object
Dim response As String
Dim json As Object
Dim translatedText As String
' Construct the API URL
url = "https://translation.googleapis.com/language/translate/v2?key=" & API_KEY & _
"&q=" & URLEncode(inputText) & _
"&source=" & sourceLang & _
"&target=" & targetLang
' Create XMLHTTP object to send the request
Set xml = CreateObject("MSXML2.XMLHTTP")
xml.Open "GET", url, False
xml.Send
' Get the response text from the API
response = xml.responseText
' Parse the JSON response
Set json = JsonConverter.ParseJson(response)
' Extract the translated text
translatedText = json("data")("translations")(1)("translatedText")
' Return the translated text
TranslateText = translatedText
End Function
' URL encode the text to ensure proper request format
Function URLEncode(str As String) As String
Dim i As Integer
Dim encStr As String
Dim charCode As Integer
Dim char As String
encStr = ""
For i = 1 To Len(str)
char = Mid(str, i, 1)
charCode = Asc(char)
' Encode characters that are safe for URLs
If charCode >= 48 And charCode <= 57 Or _ charCode >= 65 And charCode <= 90 Or _ charCode >= 97 And charCode <= 122 Or _
char = "-" Or char = "_" Or _
char = "." Or char = "~" Then
encStr = encStr & char
Else
encStr = encStr & "%" & Hex(charCode)
End If
Next i
URLEncode = encStr
End Function
- Replace “YOUR_GOOGLE_CLOUD_API_KEY” with your actual Google Cloud API key.
Explanation:
TranslateText Function:
- Translates a given text (inputText) to a specified target language (targetLang), with an optional source language (sourceLang defaults to “auto”).
- Constructs a request URL with the API key and language parameters.
- Sends the request using MSXML2.XMLHTTP, then parses the JSON response to extract the translated text.
URLEncode Function:
- Encodes the input text to make it URL-safe by replacing special characters with their percent-encoded equivalents.
Step 3: Install JSON Parsing Library
As Excel does not natively support JSON parsing, you must download VBA-JSON from GitHub to parse JSON responses from OpenAI.
- Download JsonConverter.bas from GitHub.
- Go to File tab >> select Import File in the VBA editor.
- In the Import Box >> select JsonConverter.bas to add it to the project.
Enable References:
You will need to enable Microsoft Scripting Runtime reference because the JsonConverter library relies on Dictionary objects to manage JSON data structures.
- Go to Tools tab >> select References.
- In the Available References box >> check Microsoft Scripting Runtime >> click OK.
Step 4: Use the Smart UDF Function
- Save the VBA code and return it to Excel.
- Insert the following formula in the selected cell.
Formula:
=TranslateText(A1, "de")
- A1: Contains the text you want to translate.
- “de”: Specifies the target language for translation (German in this case). You can change “de” to any other valid language code (e.g., “fr” for French, “es” for Spanish, etc.).
- Here, A1 contains the text “Hello, how are you?” it returns Wie geht es dir?
Google Translate Function with Google Translate Webpage
If you don’t have the API key, you can use the Google Translate website to create a smart translation function in Excel. The approach involves web scraping, which extracts translation results directly from the Google Translate webpage.
To insert the VBA code:
- Go to the Developer tab >> select Visual Basic.
- In the VBA editor, go to Insert tab >> select Module.
- Insert the following VBA code in the Module to create a user-defined function.
VBA Code:
Function GoogleTranslate(TextToTranslate As String, SourceLang As String, TargetLang As String) As String
Dim objHTTP As Object
Dim htmlDoc As Object
Dim URL As String
Dim Translation As String
' Construct the Google Translate URL
URL = "https://translate.google.com/m?hl=" & SourceLang & _
"&sl=" & SourceLang & "&tl=" & TargetLang & "&q=" & URLEncode(TextToTranslate)
' Create an HTTP Request object
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
objHTTP.Open "GET", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.Send
' Load the response into an HTML document
Set htmlDoc = CreateObject("HTMLFile")
htmlDoc.body.innerHTML = objHTTP.responseText
' Extract translation from the page
On Error Resume Next
Translation = htmlDoc.getElementsByClassName("result-container")(0).innerText
On Error GoTo 0
' Return the translation or an error message
If Translation <> "" Then
GoogleTranslate = Translation
Else
GoogleTranslate = "Translation failed"
End If
End Function
Function URLEncode(ByVal Text As String) As String
Dim i As Integer
Dim Char As String
Dim EncodedText As String
For i = 1 To Len(Text)
Char = Mid(Text, i, 1)
Select Case Asc(Char)
Case 48 To 57, 65 To 90, 97 To 122 ' Alphanumeric
EncodedText = EncodedText & Char
Case Else
EncodedText = EncodedText & "%" & Hex(Asc(Char))
End Select
Next i
URLEncode = EncodedText
End Function
Explanation:
GoogleTranslate sends a request to Google Translate and extracts the translation from the HTML response.
- Takes three arguments TextToTranslate, SourceLang, and TargetLang.
- Constructs a URL for Google Translate, based on source and target languages, and sends an HTTP request to Google Translate’s mobile site.
- Extracts the translated text from the response using HTML parsing (via MSXML2.XMLHTTP and HTMLFile).
- Returns the translated text or “Translation failed” if it couldn’t be retrieved.
URLEncode ensures that the text is properly formatted for a URL by converting non-alphanumeric characters into a URL-encoded format.
- Encodes special characters in the input string (Text) to make it safe for use in a URL (by converting them to their hexadecimal representations).
Use the UDF Function:
- Insert the following formula in the selected cell to translate to your desired language.
Formula:
=googletranslate(A2, "en", "de")
This formula translates the texts from English to German.
=googletranslate(B2, "de", "fr")
This formula translates the texts from German to French.
=googletranslate(A2, "en", "es")
This formula translates the texts from English to Spanish.
Conclusion
By following the above steps and procedure you can seamlessly translate text in Excel using Google’s Translate API. Google Translate webpage is also useful for creating smart functions. both of the platform creates smart translation functions that are versatile, efficient, and easy to use, saving time and effort.
