Search code examples
google-sheetsgoogle-translategoogle-sheets-formulagoogle-translation-api

Is there any way to skip particular words from translation in 'GoogleTranslate()' function in spreadsheets?

I am using GoogleTranslate() with Sheets to translate some contents into different languages. In those contents, we have some placeholders that do not need to translate. E.g.: 

This is a Sample Text added by `{__NAME__}` on `{__DATE__}`

I do not need to translate placeholders like {__NAME__} and {__DATE__}.

How can I skip those words from the translation process?

Solution 
  • =ARRAYFORMULA(QUERY(IFERROR(VLOOKUP(TRANSPOSE(SPLIT(GOOGLETRANSLATE(QUERY(
 REGEXREPLACE(TRANSPOSE(SPLIT(A1, " ")), "\{.*\}", "♦"),, 999^99), "en", "es"), " ")),
 {REGEXREPLACE(TRANSPOSE(SPLIT(A1, " ")), "\{.*\}", "♦"), TRANSPOSE(SPLIT(A1, " "))}, 2, 0),
 TRANSPOSE(SPLIT(GOOGLETRANSLATE(QUERY(REGEXREPLACE(
 TRANSPOSE(SPLIT(A1, " ")), "\{.*\}", "♦"),,999), "en", "es"), " "))),,999^99))

    0