Excel 365 has two new translation functions to identify language from text or translate text from one language to another. Let’s see how the work, some problems and unknowns.
New in Excel 365 are:
DetectLanguge() to identify the language of the text supplied, hopefully correctly (see below)
Translate() converts text from one language to another.
These features have been in Google Sheets for some time, so these Excel functions aren’t so much ‘new’ as ‘catch up’. The new Excel functions work almost the same way as GoogleTranslate() which is available to anyone using Google Sheets, not just subscribers.
During the beta/Insiders testing, the two functions have changed a little.
DetectLanguage()
This function takes some text and returns what language that text is.
= DetectLanguage(“some text or cell reference”)
Returns a language code, mostly two-letters. We’ve converted that to a name in the second column.
“You know sometimes words have two meanings”
Be careful taking DetectLanguage() results at face value.
Where DetectLanguage() can mislead is with words which have different meaning in various languages or there’s words from more than one language. The results in our ‘mixed language’ tests have changed.
The two “Thanks Danke” tests returned German (de) in early versions of DetectLanguage(), now they show English (en) in both cases.
It’s hard to understand how DetectLanguage() decides which language to return – it’s not simply the first language found in the string. For example, the two “Thanks Danke” examples above.
Ideally, DetectLanguage() would return an array of possible languages rather than just one. But that’s something for a future ‘wish list’.
Translate()
Translate converts text from one language to another, with both languages specified (optional).
=Translate(“Text or cell reference”, <Language code of source text>,<Language code to translate>)
Automatic or Default language selection
Both the From and To language parameters can be blank so that Excel will use the default language set in the operating system (Windows or Mac).
=Translate(A1,,"de")
will translate the cell A1 from the computer system language into German (de)
=Translate(A1,"cs",)
will translate the cell A1 from Czech (cs) into the computer system language.
Language Codes
Both the functions use international standard language codes like this:
#CONNECT Errors
#CONNECT errors which indicate some problems with Microsoft’s cloud service (assuming there’s an Internet connection).
To try again, choose ‘Recalculate this Cell’ from the error drop-down menu.
Or just click in the formula bar and press Enter to force the cell to recalculate.
Annoyingly, none of the standard Excel recalculation options can force a Translate cell to refresh. The translate functions are ‘immune’ to Calculate Now, Calculate Sheet nor Data | Refresh All.
Unknown errors
There are two possible errors that Microsoft lists but without any essential detail.
Text too long
There’s some limit on the length of text input but Microsoft gives no indication of what’s ‘too long’. Some guide would be helpful to customers.
Daily quota / Request throttled
There’s also some limit on the amount of use allowed each day but, again, no indication of what that limit is. Also no indication of when a new ‘day’ begins, it could be the users local time, UTC/GMT, Seattle time (Microsoft’s home base) or something else entirely!
Adding to the confusion are two different error messages for the same problem.
Translate() has “Request Throttled”
DetectLanguage() has “Daily Quota”.
A LOT more to come
Peter Deegan is having nerdy fun playing with the new translate functions and especially getting them to work smoothly from their ‘bare bones’ beginnings.
Make sure you’re subscribed to Office Watch for more on how Microsoft Office really works!
Who gets it?
Some Microsoft 365 Insiders (beta) have the translation functions in these builds …
- Windows: v2407 build 16.0.17808.20000 and later
- Mac: v16.87 build 24062430 and later
As usual this is a gradual rollout. Not all Insiders have the functions, all you can do is wait.
Translate automatic or manual in Outlook
Translate a whole Word document to another language
Translate your PowerPoint speech live to multiple languages