Google Translate is an amazing feat of engineering, which uses artificial intelligence to translate speech and text from a chosen language into another. In most cases, Google Translate\u2019s own interface embedded in Google Search or on\u00a0translate.google.com<\/a>\u00a0suffices to get some ad-hoc thing translated quickly.<\/p>\n Again, as in other Case Studies presented here, Google Sheets comes to the rescue!<\/strong><\/p>\n<\/p>\n =GOOGLETRANSLATE(B16<\/span>,\u00a0<\/span>\u201cen\u201d<\/span>,\u00a0<\/span>\u201cfr\u201d<\/span>)<\/strong><\/p>\n 1. Drop-down lists for the languages<\/strong><\/span><\/p>\n Other than formatting the file to your liking, you can create some drop-down lists for the Source and Target languages.<\/p>\n This will help you being more productive as you do not need to search for the language codes every time you want to change them.\u00a0<\/p>\n In my case, I used the\u00a0Data Validation<\/b>\u00a0feature using as a criterion a List from a Range. First, I created a new sheet with all the Languages and their codes. Then, I used the column with the language names as my List for data validation.<\/p>\n Advantages of the drop-down list:<\/p>\n For your drop-down list to auto-update when you are adding new data on the criterion Range, use a formula like:\u00a0=’Language Codes’!$A$2:$A\u00a0<\/em><\/strong><\/p>\n Note that on the range defining the data that will be used for the drop-down list, I specify the beginning of the selection ($A$2: row 2 of column A) but I do not specify any row at the end of the selection (:$A).<\/p>\n I could have written the formula like this\u00a0=’Language Codes’!$A$2:$A65<\/em><\/strong>\u00a0but then, every time I am adding new languages to the list I will need to update the Data Validation formula.<\/p>\n I have now the Language selected from the drop-down list but in the formula I need to have the Language Code and not the language name. Therefore, I am using the VLOOKUP function that is searching the Language Name in the Language Code Sheet and returns the Language Code corresponding to the selection.<\/p>\n VLOOKUP($B$11,’Language Codes’!A:B,2,FALSE) – Source Language<\/em><\/strong><\/p>\n So it looks for the value in cell B11 (the source language) in the Sheet “Language Codes” on column A. Once it finds the Language, it will return the information on the next column on that specific row. The Language Code. If the language is set to English, this function will return “en”.\u00a0<\/p>\n VLOOKUP<\/span>(<\/span>$B$15<\/span>,<\/span>‘Language Codes’!$A:$B<\/span>,<\/span>2<\/span>,<\/span>FALSE<\/span>) – Target Language<\/span><\/em><\/strong><\/p>\n It looks for the value in cell B15 (the target language) in the Sheet “Language Codes” on column A. Once it finds the Language, it will return the information on the next column on that specific row. The Language Code. If the language is set to French, this function will return “fr”.\u00a0<\/p>\n So, starting from the formula definition on the yellow area, our complete formula will look like this now (check the color code on the formula definition to spot every part):<\/p>\n =<\/span>GOOGLETRANSLATE<\/span>(<\/span>A16<\/span>,<\/span>VLOOKUP<\/span>(<\/span>$B$11<\/span>,<\/span>‘Language Codes’!A:B<\/span>,<\/span>2<\/span>,<\/span>FALSE<\/span>)<\/span>,<\/span>VLOOKUP<\/span>(<\/span>$C$15<\/span>,<\/span>‘Language Codes’!$A:$B<\/span>,<\/span>2<\/span>,<\/span>FALSE<\/span>)<\/span>)<\/span><\/p>\n Since we pre-populated the entire table with formulas, the rows where we do not have a text to be translated will bring an error message #VALUE! It is not an error per se but it will make our file look ugly. Therefore, we can use the =IFERROR function and instruct Google Sheets to discard all the errors and show instead of an empty cell.\u00a0<\/p>\n Since the function definition is =IFERROR(value,<\/b><\/em>\u00a0[value_if_error]),\u00a0<\/b>we will have to<\/em><\/p>\n =<\/span>iferror<\/span>(<\/span>GOOGLETRANSLATE<\/span>(<\/span>A16<\/span>,<\/span>VLOOKUP<\/span>(<\/span>$B$11<\/span>,<\/span>‘Language Codes’!A:B<\/span>,<\/span>2<\/span>,<\/span>FALSE<\/span>)<\/span>,<\/span>VLOOKUP<\/span>(<\/span>$B$15<\/span>,<\/span>‘Language Codes’!$A:$B<\/span>,<\/span>2<\/span>,<\/span>FALSE<\/span>)<\/span>)<\/span>,<\/span>“”<\/span>)<\/span><\/strong><\/em><\/p>\n<\/p>\n Yes, we are done. We will not complicate the formula more than that.<\/p>\n \u00a0<\/p>\n Register for FREE on https:\/\/zoran.cloud<\/a> to use the Google Sheet file. If you want to make any changes or have it on your own Google Drive account, make a copy, use it and abuse it :-).<\/p>\n Zoran<\/p>\n<\/div>\n Go to Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<\/a><\/p>\n
![](\"https:\/\/zoran.cloud\/wp-content\/uploads\/2021\/04\/CS-2-Translate-with-Google-Sheets.jpg?profile=RESIZE_710x\"){kind=spacer}
<\/a><\/p>\nMain formula:<\/h1>\n
\n
How to make this even better?<\/h1>\n
\n
\u00a02.\u00a0VLOOKUP function to pick the language code<\/strong><\/span><\/h4>\n
3. CLEAN ERRORS PROACTIVELY<\/strong><\/span><\/h4>\n
\n
Final formula:<\/h1>\n