Free Excel function for translating text with Google Translate

UPDATE: Google is shutting down the free Translation API on which this solution depends. Instead, they have published a new paid API that you can access with our new translation add-in. Though it’s not free, it’s not expensive either, and it’s much faster and more reliable than the old free solution. There’s a free trial so you can test before purchasing.

We created a new VBA function that uses the Google Translate API to translate text in Excel (or other MS Office applications). The only input the function requires is the text you want translated, and the code of the language you want it translated to (for example ‘en’ for English). Google Translate can automatically detect the source language, though you may also specify it in the function parameters if you so wish. The code for the function is here, and an Excel file showing it in use is here [Links removed due to the API shut down]. The file also contains a list of supported languages.

61 Responses to “Free Excel function for translating text with Google Translate”

  1. Craig

    Great VBA function. I’ve been working with Google spreadsheets to convert simplified chinese to English and had been looking for a way to get this to work in excel.

    I can get other languages to work but can’t get the simplified chinese to translate back to english. Any suggestions?

    Reply
  2. Mikael Thuneberg

    The function currently does not work with source languages that use an alphabet other than the Latin alphabet (at least not with the Asian ones). The reason for this it that text would need to be encoded before sending it over to Google. I’ll try to fix this at some point when I find the time.

    Reply
  3. Craig

    Cheers, that makes sense. Shame as most of what I’m trying to translate is originating in Chinese, but it is still helpful for the spreadsheets we are sending back.

    Reply
  4. Anonymous

    Yes I also would be most happy to see it work in chinese. This is avery nice piece of work.

    Reply
  5. Mikael Thuneberg

    The only reason that comes to mind for the differing translations is that there’s some special character in the text your translating, and that isn’t encoded correctly by the function. If you send me the text and the language pair, I can take a look.

    Reply
  6. mike

    I would echo the earlier positive comments; this code is just what I’ve been looking for! However, I too have noticed that some languages don’t translate (to English) as well as if the text is pasted directly into Google’s Translate webpage. I’m only concerned with European languages. In your last reply you mentioned special characters; does this include accented letters used in Swedish, for example?

    Reply
  7. Peter J

    Beautiful. I do reporting for an international company and this will save me a huge amount of headache in trying to quickly gather numbers from some of our overseas call centers! Kudos to you for a well-needed script!

    Reply
  8. Vish_84

    Awesome Func…Cool stuff…while passing this function and trying to translate a lot of data it skips a few rows and continues….have not been able to find the cause…any ideas??

    Reply
  9. A Lux

    Hi
    Like the code but when I am behind Firewall does not work. Have tried the
    • Before the line objhttp.Open “GET”, URL, False you need to add objhttp.setProxy(proxySetting, varProxyServer, varBypassList)
    • After the line objhttp.Open “GET”, URL, False you need to add objhttp.setProxyCredentials(username, password)

    stuff-but cannot get it to work. Any other clues?

    Cheers

    Aris

    Reply
  10. Mikael Thuneberg

    Hi,

    If the firewall is blocking it, there probably isn’t anything you can do in Excel to bypass it. You have to check the firewall settings and give Excel the rights to access the internet.

    Reply
  11. Paul Kelly

    Hi great function.

    I am experimenting running it against a lot of rows. After about 100 or so I start to get an error, Language Code not available. I assume it is a timeout issue.

    Reply
  12. Paul Kelly

    Hi Mark,

    I have managed to solve the timing problem, but I have a new one. Google seems to be blocking me. I amgetting a 403 abuse of service message. I have read somewhere that this might be solved by some form of authentication in Javascript.

    What can we do?

    Thanks.

    Reply
  13. Mikael Thuneberg

    Running a lot of translations from Excel may be the kind of automated requests not allowed by the API terms of service. Perhaps there is some kind of solution to this other than making the script wait until the block is lifted – if you want to hire me to try this please contact me by email. Thanks.

    Reply
  14. Adam

    I see in the source file that this script pulls
    from google:

    {“responseData”: {“translatedText”:”Library”,”detectedSourceLanguage”:”es”}, “responseDetails”: null, “responseStatus”: 200}

    there is referece to a detected source language. How can this been pulled out and put say in the cell next to text to be translated.

    Reply
  15. Joe

    Cool!

    I’m getting different results as well.
    at translate.google.com, “Dosier grenze geändert” becomes “Metering limit changed”, whereas in Excel it becomes “Dosing last frontier”. I’m looking for the first translation…

    Reply
  16. Mikael Thuneberg

    Hi Joe,

    When I try it through the Excel, it returns “Metering limit changed”, the same I get through the website. I don’t know why you get a different results – difficult to investigate as I can’t reproduce it here..

    Reply
  17. Joe

    Hello,

    Yeah — interesting. It changed to “metering limit changed” for me as well. Not sure what the difference was.

    Thanks!

    Reply
  18. Anonymous

    Hello,
    Great work.

    But long sentences are truncated, which is not the cas in Google Web interface.
    DO you have a length limit with your stream object ?
    Regards

    Reply
  19. Mikael Thuneberg

    Yes, it truncates the responses to 255, because at least in some version of Excel, that’s the maximum number of characters that a VBA script can put into a cell. You can try removing this limit on by changing this line in the code:

    translate = Left(Left(translate, InStr(1, translate, Chr(34)) – 1), 255)

    to this:

    translate = Left(translate, InStr(1, translate, Chr(34)) – 1)

    Reply
  20. Alastair

    For some reason I can’t get this to work – either from your example workbook, or by copying the code into my own.
    The code breaks at objHttp.Send (“”) and I get the error:
    “Run-time erro ‘-2147012867 (80072efd)’:
    A connection with the server could not be established”

    Any ideas?

    Reply
  21. Anonymous

    Here’s how to get it to work through a proxy…

    Set objhttp = CreateObject(“MSXML2.ServerXMLHTTP.6.0”)
    objhttp.setProxy 2, “proxy:8080”, “”

    Reply
  22. Anonymous

    Hi,
    Nice one. But I think there is character limit in the URL. Can you please let me know the number of characters it will accept. Is there any way I can use unlimited characters in the URL.
    Thanks.

    Reply
  23. SeanWF

    Trying to run the function always causes Excel to freeze completely until I close the program. This occurs when using either the .xls file you’ve linked to, or the VBA. Any thoughts?

    Reply
  24. Bertrand

    Hi Mikael, thanks very much for the function. It helps a lot.

    I am having issue with the line breaks in the cell that needs to be translated.

    I get the following error message:
    TLE>Bad Request< /TITLE >< /HEAD >< BODY BGCOLOR= Any idea? Thanks a lot. Bertrand

    Reply
  25. Anonymous

    This script looks great, but I’m trying to use it in Excel 2011 on Snow Leopard and it keeps giving me the “error in value” error, with #VALUE! in all cells, even in your demo example, even if i type the commands manually. The program even recognizes “translate” as a valid command. There is no firewall blocking and the program seems to work with other VBA scripts I have found, both xls and xlsm format. I wish I knew more about VBA so I could help debug, because it is possible that these errors are being caused by excel 2011 idiosyncrasies…It seems like #VALUE! is a pretty catch-all error–I would be willing to provide any other necessary details.

    Reply
  26. Minnoy Alain

    How can I suppress the ‘Language Not Found’ when making multiple translations in a short sequence?

    Is there a way to delay and wait until the block is removed using the http object?

    Thks in advance,
    This code is really great but I can only use it if it works all the time.

    Reply
  27. Mikael Thuneberg

    Anon: this does not work yet in Mac Excel 2011, that would require some quite significant changes to the code, and currently I have no plans to put time into it.

    Nauman: did you wrap the text and language code in quotation marks?

    Reply
  28. Nauman Khan

    yup, i tried that, somehow nothing worked for me, i have started using google docs’s spreedsheet with translation formula to fulfill my need

    Reply
  29. Anonymous

    Add an “userip” parameter in the end of the URL, ex. URL = “http://ajax.googleapis.com… & resultLanguageCode & “&userip=192.168.22.23”

    Reply
  30. Anonymous

    It works great but…
    Are you sure this is within the Google Translate API terms of service, Mikael? It looks like “automation” is not allowed — isn’t making use of it in VBA automation?
    Their developer’s guide seems to be aimed at its use with websites and use of an “API key” and/or valid “HTTP referer” doesn’t seem to lend itself to use with VBA.
    Your comments would be much appreciated.

    Reply
  31. Mikael Thuneberg

    Is a spreadsheet more “automatic” than a website? Personally I don’t think Google would object to this, at least when it’s used on a small scale, but if you think it’s against the TOS then don’t use it.

    Reply
  32. Anonymous

    The script’s great, but google translate is somehow buggy – my output language is always set to EN, but randomly google translate switches the output to my native language. i tried changing the script to to_language_code = “EN”, but this doesn’t affect the way it behaves.
    No matter how i try, i cannot make the script to always produce results in English – can provide some help on this matter?

    Reply
  33. Anonymous

    “The Google Translate API has been officially deprecated as of May 26, 2011. Due to the substantial economic burden caused by extensive abuse, the number of requests you may make per day will be limited and the API will be shut off completely on December 1, 2011.”

    It’s a shame.. I really liked the functionality! Any idea how many requests I can send per day?

    Reply
  34. Anonymous

    I’ve got an excel sheet of about 6.000 cells I wanted to translate.

    I’ll give it a try and let you know how many I can do per day.

    Reply
  35. Don

    To solve the line break issue include the following code in the case switch:

    Case 0 To 15
    sTemp = “%0” & Hex(iAsc)

    Reply
  36. Shafiq

    Any Idea that how this API could give me the source code of the language I am translating. I am looking for the API that identify the language or give me the source code of the language I am translating.

    Reply
  37. Micha_el

    the example file doesnt seem to work, tried it with openoffice and excel.
    can I just download, open, activate macros and it shoud work?

    Reply
  38. Anonymous

    Hello Mikael do you know if it is still working? I tried to use it with very poor result and your example only works with few languages…

    However great idea! Hope Google will change opinion about it.

    Reply
  39. Anonymous

    It used to be great but this version 1 API code now works sporadically (as warned by Google). Although it’s a pity that the free service is disappearing, it looks simple to modify the HTTP request to suit the v2 API requirements (including using a key). Don’t you agree Mikael?

    Reply
  40. Mikael Thuneberg

    Yes, but in this case, using it with functions is no longer ideal, as the functions may often update unnecessarily, which will now cost money with the new paid API. I’m going to release a new Excel addin that uses the new API version. There are also other benefits compared to the old function approach, eg. it works up to 40 times faster and is much more reliable.

    Reply

Leave a Comment to Minnoy Alain

  • (will not be published)