空白と数字を翻訳しないGoogleSpreadシートでの翻訳する関数例

Photo by Lukas on Pexels.com

Google translateホント便利だねー。色々便利だけど、Spead SheetでもGoogle Translateが使えるのがすごい。

全部の列に日本語が入ってて、全部英語にするならいいけど、そうじゃないケースもあると思う。そこで私の工夫したことをシェアするね。

そもそも、なんでこんなことしたかというと、日本語で書いた膨大なMoodleのquiz(小テスト)をCSVでアップロードをしたかった。

一つ一つ翻訳したけど、すっごい大変。スプレッドシートで一括で変換すれば楽じゃん!ってことになった。

まず基本。Googleスプレッドシートの日本語を英語に翻訳する関数

Googleスプレッドシートの日本語を英語にしたいときは専用の関数がある。

このブログ「Googleスプレッドシートで英作文が捗る!と聞いたので試してみました」を参考にしたのだけど、要するに

=googletranslate(訳したいセル,”ja”,”en”)

https://ej.alc.co.jp/entry/20170303-gnmt-spreadsheet

と書く。セルと元の言語(日本語)と翻訳したい言語(英語)を設定すると、そのセルが翻訳される!コピーして全シートを翻訳すると全部英語になる!

うわーめちゃカンタン!

でもね、いくつか問題がある。

空欄はエラーになってしまう

空欄があると翻訳できないからエラーになる。翻訳したい箇所だけ翻訳する関数を入れれば良いのだけど、それだと隙間があるたびに外さなきゃいけないので大変。CSVファイルにしたら、そのままエラーとなるだろうから、空欄を外す必要がある。

その対策としてIFをつかって空白は空白のままにした

if(翻訳するセル=””,””,(googletranslate(訳したいセル,”ja”,”en”))

これで空欄は””つまり空白のまま。それ以外は翻訳される。

しかし別な問題がある。

数字が英語になってしまう

4を翻訳するとfourになる。123はone two threeになる。
でも数字によってはそのまま数字で翻訳してくれる。

これ、どこまでが数字が数字のままになり、どこからがアルファベットになるかが不明なのだけど、CSVにするにはこれもまた困ってしまう。

数字のセルは数字のままにするにはどうするか。

そこで次の関数を加えた。

ISNUMBER(翻訳するセル)

これをいれると、数字だとTrue 数字じゃないとFalseになるようだ。

これを活用するIF文をつくる。

if(ISNUMBER(翻訳するセル),翻訳するセル,(GOOGLETRANSLATE(翻訳するセル,”ja”,”en”)))

これで、数字が数字のまま、数字以外は翻訳される。

空白と数字を翻訳しないGoogleSpreadシートでの翻訳する関数

そこでこの2つを合成すると

if(翻訳するセル=””,””,if(ISNUMBER(翻訳するセル),翻訳するセル,(GOOGLETRANSLATE(翻訳するセル,”ja”,”en”))))

もうちょっと賢い書き方はありそうだけど、とりあえずこれで動いた。

これで空白や数字がたくさんあるGoogleスプレッドシートの翻訳で楽してもらえれば幸い。

Toshiaki Ejiri: Born in Fukushima, working as web analytics consultant since 2000.