SUBSTITUTEで文字列を置換する

データでは、数式内で検索と置換が必要になることがよくあります。電話番号からハイフンを取り除いたり、単語を置き換えたり、余分な記号を削除したりする場合です。

SUBSTITUTE関数は、セル内のテキストの一部を見つけ、元のセルを変更せずに、該当するすべての箇所を新しいテキストに置き換えます。

これは「検索と置換」に相当する数式ベースの機能ですが、データが変わると自動的に更新されます。

SUBSTITUTEは最大4つの引数を取ります。

• text元の文字列またはセル
• old_text検索するテキスト
• new_text置き換える新しいテキスト
• instance_num（省略可能）置換する出現箇所

構文は=SUBSTITUTE(text, old_text, new_text, [instance_num])です。最後の引数を省略すると、すべての出現箇所が置き換えられます。

よく使われる方法は、何もない文字列に置き換えて削除することです。これには、new_textに空の文字列""を指定します。

A2に555-123-4567が入っている場合、次の数式ですべてのハイフンを削除し、5551234567を返します。

この方法は、電話番号、ID、その他の記号を含む値の整形に使えます。

SUBSTITUTEは、1文字だけでなく単語全体にも使えます。

A2にAnnual report 2023が入っている場合、単語を置き換えて年を更新できます。

結果はAnnual report 2024になります。セル内に一致する箇所が複数あれば、すべて変更されます。

重要な点として、SUBSTITUTEは大文字と小文字を含め、テキストを完全に一致させて検索します。

A2にCat cat CATが入っている場合、=SUBSTITUTE(A2, "cat", "dog")で変更されるのは小文字のcatだけで、結果はCat dog CATになります。

大文字と小文字を区別せずに置換する必要がある場合は、まずLOWERまたはUPPERで大文字・小文字を統一するか、Sheetsの次のレッスンで学ぶ正規表現関数を使用します。

省略可能な4番目の引数instance_numを使うと、すべての出現箇所ではなく、特定の1箇所だけを対象にできます。

A2にa-b-c-dが入っていて、2番目のハイフンだけを変更したい場合は、instance_numに2を指定します。

結果はa-b/c-dとなり、2番目のハイフンだけがスラッシュに変わります。

テキストとして保存されたインポート済みの金額$1,250.00を考えてみましょう。使用できる数値に変換するには、ドル記号とカンマを取り除く必要があります。

SUBSTITUTEを2つネストし、一方をもう一方の内側に入れて、両方の文字を削除します。

内側の呼び出しでカンマを削除し、外側の呼び出しでドル記号を削除すると、1250.00が残ります。

必要な数だけSUBSTITUTEをネストし、それぞれで異なる文字を整形できます。

スペース、ドット、スラッシュを含む乱れたコードを、ハイフンで区切られた形式に統一するには、3つの置換を連結します。

各層の結果が次の層に渡され、最終的に整形済みの文字列が作られます。

SUBSTITUTEと、よく似たREPLACE関数を混同しないでください。

• SUBSTITUTEは、内容を一致させてテキストを置き換えます（この単語を見つけて置き換える）。
• REPLACEは、位置を指定してテキストを置き換えます（5文字目から4文字を置き換える）。

何を検索するかが分かっている場合はSUBSTITUTEを、どこにあるかが分かっている場合はREPLACEを使用します。

便利な応用として、SUBSTITUTEとLENを組み合わせると、ある文字が何回現れるかを数えられます。

元の文字列の長さから、文字を削除した後の長さを引きます。A2にa,b,c,dが入っている場合、この方法でカンマの数を数えると3になります。

この方法は、区切り文字で分けられたリストの項目数を数えるのに便利です。

A2に555.123 4567のような、ドットやスペースを含む乱れた電話番号が入っているとします。これを555-123-4567のような統一されたハイフン形式にしたいとします。

まずドットとスペースを取り除き、その後ハイフンを挿入することもできます。ここでは、区切り文字をハイフンに統一します。

置換をネストすると、どのような不要な区切り文字が含まれていても形式を統一できます。

SUBSTITUTEの理解度を確認しましょう。

数式でテキストを検索して置換する方法を学びました。

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• new_textに""を使用すると、文字を削除できます。
• 大文字と小文字が区別されるため、必要に応じて先に大文字・小文字を統一します。
• 省略可能なinstance_numを指定すると、1つの出現箇所だけを対象にできます。
• 呼び出しをネストすると複数の文字を整形でき、LENと組み合わせると出現回数を数えられます。

これで「テキストの結合と変更」を完了しました。整然とした一貫性のあるデータを作成できました。