Excel VBA to Translate Language with Formula (With Easy Steps)

Codes for Different Languages

Here’s a short list of language codes for some languages. You will find the detailed list on the Google Cloud web link.

Language Name	Language Code
Afrikaans	af
Arabic	ar
Bengali	bn
Chinese (Simplified)	zh-CN or zh
Croatian	hr
Danish	da
English	en
French	fr
German	de
Hindi	hi
Italian	it
Japanese	ja
Korean	ko
Latin	la
Polish	pl
Portuguese	pt
Russian	ru
Spanish	es
Thai	th
Urdu	ur

Excel VBA to Translate Language with Formula: Step-by-Step Procedures

Our sample dataset contains some text and their source and target language codes. We will translate these texts from the source language to the target language with a formula.

Step 1 – Create a User Defined Function in Excel

Go to the Developer tab from Ribbon.
Select Visual Basic.

The Visual Basic Editor window will appear.
Select the Insert tab.
Select Module.

A Module will open.
Copy the following code in that Module.

Function translate_text$(text_str$, src_lang$, trgt_lang$)
Dim s1&, s2&, url_str$, rsp$
Const rslt_div$ = "<div class=""result-container"">"
Const url_temp_src$ = "https://translate.google.com/m?hl=[from]&sl=[from]&tl=[to]&ie=UTF-8&prev=_m&q="
url_str = url_temp_src & WorksheetFunction.EncodeURL(text_str)
url_str = Replace(url_str, "[to]", trgt_lang)
url_str = Replace(url_str, "[from]", src_lang)
rsp = WorksheetFunction.WebService(url_str)
s1 = InStr(rsp, rslt_div)
If s1 Then
s1 = s1 + Len(rslt_div)
s2 = InStr(s1, rsp, "</div>")
translate_text = Mid$(rsp, s1, s2 - s1)
End If
End Function

How Does the Code Work?

We created a function named translate_text and declared the arguments for the function. The $ sign means as String.
Here, text_str is the text you want to translate, scr_lang refers to the language from which you want to translate, and trgt_lang refers to the language to which you want to translate.
We used the div class to get the result or the translated text from HTML and declared it as constant.
We declared another constant named url_temp_src and set it as the Google Translate link.
We used the WorksheetFunction.EncodeURL method to make sure that the data will be URL encoded.
We used the Replace function to replace the source and target language code in the URL.
The WorksheetFunction.WebService method calls the web service using HTTP GET request and return response.
Next, the InStr function finds the position of the first occurrence of a string within another string.
We used an If Statement to check for a logical rest and give results accordingly.
The Mid function gets the translated text.

Save the code and go back to your worksheet.

Step 2 – Use the Formula to Translate Language

Select the first cell where you want to get the translated text. We selected cell E6.
Insert the following formula.

=translate_text(B6,C6,D6)

Press Enter to get the result.

Here, in the translate_text function, we selected cell B6 as text_str, C6 as src_lang, and D6 as trgt_lang. The formula will translate the text in B6 from the language in cell C6 to D6.

Drag the Fill Handle down to copy the formula to the other cells.

We have copied the formula to the other cells and got the desired output.

Step 3 – Check If Formula Is Returning Correct Values

Select the cell where you want to check the language. We selected cell E6.
Go to the Review tab.
Select Translate.

The Translator will appear on the right side of the screen.
Select the source language. We selected English because we translated the first cell from English.
You can see that the translation is giving the correct value.

Things to Remember

For this formula to work, you must have your internet connection on. Otherwise, it will return a #VALUE! error.
Whenever working with VBA, you must save your Excel file as Excel Macro-Enabled Workbook. Otherwise, the VBA code will not work.

Practice Section

We have provided a practice sheet so you can practice how to use Excel VBA to translate language with formula.

Download the Practice Workbook

Creating Translate Function.xlsm

<< Go Back to Translate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Function TranslateText(text_str As String, src_lang As String, trgt_lang As String) As String Dim url As String Dim xmlhttp As Object Dim response As String Dim json As Object ' Construct the URL for Google Translate API url = "https://translation.googleapis.com/language/translate/v2?key=YOUR_API_KEY&q=" & _ VBA.URLEncode(text_str) & "&source=" & src_lang & "&target=" & trgt_lang ' Send HTTP request to Google Translate API Set xmlhttp = CreateObject("MSXML2.XMLHTTP") xmlhttp.Open "GET", url, False xmlhttp.send ' Parse the JSON response response = xmlhttp.responseText Set json = JsonConverter.ParseJson(response) ' Extract and return the translated text TranslateText = json("data")("translations")(1)("translatedText") End Function

14 Comments

Mukibul Hasan Mar 3, 2023 at 8:53 AM

very nice & helpful.
- Reply
  Shamima Sultana Mar 5, 2023 at 11:48 AM
  
  Dear Mukibul Hasan,
  
  Thanks for your appreciation. Stay in touch with ExcelDemy.
  
  Regards
  Shamima Sultana | Project Manager | ExcelDemy
Husain Dini Mar 7, 2023 at 2:28 PM

Hello there,
thanks for the VBA code,

can you please guide me on error as “Name?” for4 Translate_test formula
- Reply
  Rabeya Islam Mar 9, 2023 at 1:09 PM
  
  Dear HUSAIN DINI,
  
  Thanks for sharing your concern with us.
  
  The problem you are facing may occur if you misspell the name of the function in the worksheet cell.
  
  Another reason can be the use of incorrect syntax. Make sure to use all the arguments for the function in the correct order and separate them by a comma.
  
  For better understanding, you can share your Excel file with us in Exceldemy Forum, then we can find the specific problem in your file and solve that.
  
  Don’t forget to check our other articles for your Excel guidance.
  
  Regards,
  Rabeya Islam | Excel & VBA Content Developer | ExcelDemy
ابو معاذ Mar 3, 2024 at 10:12 PM

Thank you very much for this information. I found this code after a long search. Greetings to you.
- Reply
  Shamima Sultana Mar 7, 2024 at 9:08 AM
  
  Hello ابو معاذ,
  
  You are most welcome.
  
  Regards
  ExcelDemy
NILANJAN Apr 11, 2024 at 8:45 PM

Hello Mashhura Jahan.. this is a simple and but effective way for Excel on Windows , however I am having trouble using this with Excel on Macbook. could you please help me.
- Reply
  Shamima Sultana Apr 16, 2024 at 2:46 PM
  Hello Nilanjan,
  
  Excel for Mac doesn’t support the WorksheetFunction.WebService method due to differences in how Excel for Mac interacts with web services compared to Excel for Windows. In Mac, you need to directly access Google Translate’s API using HTTP requests or using a different method provided by Google Translate.
```
Function TranslateText(text_str As String, src_lang As String, trgt_lang As String) As String
    Dim url As String
    Dim xmlhttp As Object
    Dim response As String
    Dim json As Object
    
    ' Construct the URL for Google Translate API
    url = "https://translation.googleapis.com/language/translate/v2?key=YOUR_API_KEY&q=" & _
            VBA.URLEncode(text_str) & "&source=" & src_lang & "&target=" & trgt_lang

    ' Send HTTP request to Google Translate API
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    xmlhttp.Open "GET", url, False
    xmlhttp.send
    
    ' Parse the JSON response
    response = xmlhttp.responseText
    Set json = JsonConverter.ParseJson(response)
    
    ' Extract and return the translated text
    TranslateText = json("data")("translations")(1)("translatedText")
End Function
```
  Replace “YOUR_API_KEY” with your actual Google Cloud Platform API key.
  Remember to enable the “Microsoft XML, v6.0” reference in the VBA editor (under Tools > References) to use CreateObject(“MSXML2.XMLHTTP”) for making HTTP requests.
  
  Regards
  ExcelDemy
Alan Ang May 2, 2024 at 9:22 AM

Thank you very much very helpful for me when I create XLM Tools in Excel for multipl countries in Asia.
Not sure if you can help me on the below language that cannot work
1. Simplified Chinese ?
2. Traditional Chinese (zh)
3. Central Khmer (km or khm)
The rest works really well for Korean, Japanese, Thai, Indonesian….etc
Thanks in advance if you can advise or help me with this.
- Reply
  Lutfor Rahman Shimanto May 2, 2024 at 11:14 AM
  
  Hello Alan Ang
  
  Thanks for visiting our blog and asking an interesting question. You seek the language codes for Simplified Chinese, Traditional Chinese, and Central Khmer. Please check the following table:
  
  Language Language Code
  
  English en
  
  Simplified Chinese zh-CN
  
  Traditional Chinese zh-TW
  
  Central Khmer km
  
  You can easily translate and fulfil your goal using the above language codes and the user-defined function mentioned in this article.
  
  I hope you have found the language code you were looking for. I have attached the workbook used to investigate your question; good luck.
  
  DOWNLOAD WORKBOOK
  
  Regards
  Lutfor Rahman Shimanto
  ExcelDemy
Paolo Jul 12, 2024 at 1:31 PM

This is truly a great solution!
Easy and efficient!
Thank you very much!
- Reply
  Shamima Sultana Jul 13, 2024 at 11:05 AM
  
  Hello Paolo,
  
  You are most welcome. We are glad to hear that you found our solution easy and efficient. Keep finding Excel solutions with ExcelDemy.
  
  Regards
  ExcelDemy
Hans Hallebeek Oct 29, 2024 at 4:29 PM

Great tip and it works especially since my version of Excel does not support the TRANSLATE() function, but I made a minor modification to your great code:

Function translate_text$(text_str$, src_lang$, trgt_lang$)
Dim s1&, s2&, url_str$, rsp$, translatedtext$
Const rslt_div$ = “”
Const url_temp_src$ = “https://translate.google.com/m?hl=[from]&sl=[from]&tl=[to]&ie=UTF-8&prev=_m&q=”
url_str = url_temp_src & WorksheetFunction.EncodeURL(text_str)
url_str = Replace(url_str, “[to]”, trgt_lang)
url_str = Replace(url_str, “[from]”, src_lang)
On Error GoTo no_GO
rsp = WorksheetFunction.WebService(url_str)
s1 = InStr(rsp, rslt_div)
If s1 Then
s1 = s1 + Len(rslt_div)
s2 = InStr(s1, rsp, “”)
translatedtext = Mid$(rsp, s1, s2 – s1)
GoSub correct_special_chars
translate_text = translatedtext ‘Mid$(rsp, s1, s2 – s1)
End If
no_GO:
Err.Clear
On Error GoTo 0
Exit Function
correct_special_chars:
‘* some apostrophe show up as '
If InStrRev(translatedtext, “'”) > 0 Then translatedtext = Replace(translatedtext, “'”, “‘”)

Return
End Function

This way the aposthophe like in French l’eau will not show up as l&;’eau
- Reply
  Shamima Sultana Oct 29, 2024 at 6:20 PM
  
  Hello Hans Hallebeek,
  
  You are most welcome. Thanks for your feedback and the tip. It will be useful for our users. Keep sharing Excel tips with ExcelDemy!
  
  Regards
  ExcelDemy