Saturday, February 12, 2011

Quick Translations for an Entire Word List

I have been a fan of Google's translation tools for some time. Recent developments in GoogleDocs includes spreadsheet formulas which will allow you to enter a word into one cell and view its translation in another cell.

Here is one example of this technique:


In Column A of this GoogleDocs spreadsheet, you may enter any foreign word or phrase. In Column C, you it's English translation is revealed. What about Column B? It's there, but I have adjusted the width so it is almost invisible. The formulas in Column B are actually quite important.

You can duplicate this spreadsheet for yourself. After creating a new GoogleDocs spreadsheet, you will need to create two formulas:

In cell B2, enter =IF(A2="","",DetectLanguage(A2))
In cell C2, enter =IF(A2="","",GoogleTranslate(A2,B2))

Next, you will need to take these two formulas and "fill down" as many rows as desired. Finally, adjust the width of Column B so that it is as narrow as possible.

Let's take a look at what is happening with those formulas.
The part of the formula which says "=IF(A2="",""..." is simply saying if the cell if Column A is blank, leave the corresponding cells in Column B and Column C blank as well.

The real meat is in the last part of the formula. The "DetectLanguage (A2)" returns an abbreviation for the language. I am not really interested in that part, so I made the column very narrow. Having that value, however, is important for the calculation which happens in Column C.

The portion of the second formula which reads "GoogleTranslate(A2,B2)" instructs the spreadsheet to look at the value in A2 and translate if from the language indicated in B2 into English.

The advantage of this spreadsheet over the translation tools which have long been a part of Google is that you may enter an entire list of words or phrases and see all of their translations at one time.
Post a Comment