いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Gスプレッドシート)複数の種類の文字を一括して変換する

 Googleスプレッドシートで、複数の種類の「文字」の変換を一つの式で行う方法についてです。式が長くてやや実験的な内容になりますが、式のネストに頼ることなくA→B,C→D,E→Fといったように複数の変換を一度に行うことができます。文字列中に同じ文字が何度現れてもすべて変換されます。
 ただし、ここで可能なのは文字単位の変換のみで、2文字以上の文字列の変換には対応できません。

  • 各種関数の組み合わせにより、複数の種類の文字変換を一括して行うことができます。
  • 「ャュョッ」などの小さいカナを大きいカナに変換するなどの使用例が考えられます。
  • 変換の組み合わせが少ない場合はSUBSTITUTE関数のネストに頼った方が便利です。
  • 変換先が常に一定の場合はREGEXREPLACE関数を使えば簡単です。

手順

「一」「二」「三」を「壱」「弐」「参」に変換する例

 画像ではB3セルに漢数字が入力されています。
 そこで、文字列に含まれる「一」「二」「三」をそれぞれ「壱」「弐」「参」に変換するため、C3セルに式を入力しています。

f:id:accs2014:20200524031259p:plain:w780

 式の内容は次のようなものです。

=CONCATENATE(ARRAYFORMULA(MID("壱弐参"&B3,FIND(MID(B3,SEQUENCE(LEN(B3)),1),"一二三"&B3),1)))

 ちょっと長くてややこしい式ですが説明します。
 対象文字列を1文字ずつ分解したうえで「一二三+対象文字列(B3)」という文字列の中でその文字が最初に見つかる位置をぞれぞれ取得します。一方で「壱弐参+対象文字列(B3)」という文字列から同じ位置にある文字を取得し、最後に連結しています。結果的に一・二・三の三種の文字のみが壱・弐・参に変換され、他の文字はそのままになります。ダブルクォーテーションで囲まれた2つの文字列は変換リストとなっており、双方の文字数と順番を揃える必要があります。

f:id:accs2014:20200524031303p:plain:right:w450

 結果はこうなります。

f:id:accs2014:20200524031307p:plain:right:w450

 式を下方にフィルコピーしたようすです。
 同じ文字が複数回現れてもきちんと変換されています。

小さいカナを大きいカナに変換する例

 画像は省略しますが、次の式によりB3セルに入力されている文字列中の小さいカナ(ャュョなど)をすべて大きいカナに変換できます。

=CONCATENATE(ARRAYFORMULA(MID("アイウエオカケツヤユヨワ"&B3,FIND(MID(B3,SEQUENCE(LEN(B3)),1),"ァィゥェォヵヶッャュョヮ"&B3),1)))

 上記の例と違うのはダブルクォーテーション内の文字列の内容(2か所)だけです。
 変換の組み合わせは12組に増えていますが、式がさほど長くならないのがメリットです。

備考

 最初の例のように変換の組み合わせが少ない場合はSUBSTITUTE関数のネストで対応した方が簡単ですし、2文字以上の文字列の変換にも対応できますので便利です。一方で2番目の例の場合や、「半角英字をすべて全角英字にする」といったように変換の組み合わせが数十に及ぶ場合にはSUBSTITUTE関数だと式が非常に長くなるため、ここで示した例のような方法が有利になってきます。
 また、変換先が常に同じ文字(文字列)である場合はREGEXREPLACE関数で簡単に対応できます。詳しくは次の記事をご覧ください。

www.officeisyours.com