1

I am using GoogleTranslate() function to translate one language to another in Google spreadsheets. I have a sentence in one (known) language at column A1 'Lorem ipsum dolor sit amet' and i want the translation of it in English at column B1. so i used this:

=GoogleTranslate(A1, "li", "en")

The result is: 

"Lorem ipsum dolor sit amet".

There is double quotes now. I want the translation to be with single quote (like before), I found its Google's bug (yes, i said it Google's bug lol). So the solution maybe in within regular expression or something else...

How can I replace the double quotes (in the being and the end only) of the translated sentence in Google spreadsheets?

ross
  • 2,684
  • 2
  • 13
  • 22
yanivz
  • 158
  • 1
  • 13

3 Answers3

1

Based on the docs it looks like Google Spreadsheets only allows you to search with regex and not replace, but in case you actually can:

Find: "(.*?)"
Replace: '\1'

You can of course also do this with some other program using the same regexen.

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
1

You could try something like...

=SUBSTITUTE(A2,"''","'")

This (perhaps overly-simple?) function will replace two adjacent single quotes with one single quote. So you could combine them and end up with:

=SUBSTITUTE(GoogleTranslate(A1, "li", "en"),"''","'")

It's not the most elegant, but I think it should work...

Todd Kerpelman
  • 16,875
  • 4
  • 42
  • 40
0

it seems li is no longer supported:

enter image description here

see the list of supported locales at: https://stackoverflow.com/a/73767720/5632629

player0
  • 124,011
  • 12
  • 67
  • 124