スプレッドシートのSUBSTITUTE関数の使い方｜文字列置換の基本から応用まで

スポンサーリンク

スプレッドシートで「この文字を別の文字に置き換えたい」と思ったことはありませんか？電話番号のハイフンを消したい、データのカッコを一括で外したい。手作業で直すと時間がかかりますし、何百行もあると修正漏れが怖いですよね。

SUBSTITUTE関数を使えば、指定した文字列をまとめて置換できます。この記事ではスプレッドシートでのSUBSTITUTE関数の基本から、複数文字の一括置換、似た関数との使い分けまで丁寧に解説します。

スプレッドシートのSUBSTITUTE関数の使い方：基本構文と引数の役割

SUBSTITUTE関数は、文字列の中から指定した文字を別の文字に置き換える関数です。読み方は「サブスティテュート」。英語の「Substitute（代わりに置く）」が名前の由来です。

たとえば「東京都渋谷区」の「渋谷」を「新宿」に置き換えて「東京都新宿区」にできます。Excelの「検索と置換」機能のように使えますが、数式なので元データを壊さずに結果を得られるのがポイントです。

4つの引数の役割

=SUBSTITUTE(対象テキスト, 検索文字列, 置換文字列, [出現回数])
引数必須/省略可説明
対象テキスト必須置換元のセルまたはテキスト
検索文字列必須置き換えたい文字列
置換文字列必須置き換え後の文字列（空文字””で削除）
出現回数省略可何番目の一致だけを置換するか（省略で全置換）

第4引数は省略するケースがほとんどです。省略すると一致する文字列をすべて置き換えます。

基本的な使い方

セルA2に「2024/03/18」が入っているとします。

=SUBSTITUTE(A2, "/", "-")  → 「2024-03-18」

スラッシュをハイフンに置き換えました。対象テキストに「/」が2つありますが、第4引数を省略しているので両方とも置き換わります。

置換文字列に空文字（””）を指定すれば、文字の削除としても使えます。

=SUBSTITUTE(A2, "/", "")  → 「20240318」

検索文字列が見つからなくてもエラーにはなりません。元テキストがそのまま返るだけなので、「置換できていない」事故に気づきにくいのです。

SUBSTITUTE関数でよく使う実務パターン

スペース・改行・不要文字を削除する

データのコピペで混入する余分なスペースや改行は、SUBSTITUTE関数で一掃できます。

=SUBSTITUTE(A2, " ", "")       → 半角スペースを削除
=SUBSTITUTE(A2, "　", "")      → 全角スペースを削除
=SUBSTITUTE(A2, CHAR(10), "")  → 改行を削除

改行は目に見えないので直接入力できません。CHAR(10)で改行コードを指定するのがコツです。

電話番号のハイフンを消す

電話番号を「数字だけ」にしたい場面はよくありますよね。

=SUBSTITUTE(A2, "-", "")
_images/spreadsheet-substitute-function/02_formula_substitute-basic.png

A2に「03-1234-5678」が入っていれば「0312345678」が返ります。ハイフンが2つあっても全置換なので1回の数式で完了です。

!_images/spreadsheet-substitute-function/01_data_phone-numbers.png

複数の文字列を一括置換する（ネストSUBSTITUTE）

SUBSTITUTE関数は1回の呼び出しで1種類の文字しか置換できません。複数の文字を置き換えたい場合は、SUBSTITUTE関数を入れ子（ネスト）にします。

たとえばA2に「(株)ABC商事」が入っていて、カッコを両方消したい場合です。

=SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", "")  → 「株ABC商事」

!_images/spreadsheet-substitute-function/04_formula_substitute-nest.png

ちょっとむずかしく見えますが、やっていることはシンプルです。

  1. 内側のSUBSTITUTE(A2, "(", "")で「(」を削除 → 「株)ABC商事」
  2. 外側のSUBSTITUTE(..., ")", "")で「)」を削除 → 「株ABC商事」

!_images/spreadsheet-substitute-function/05_result_substitute-nest.png

3つ以上の文字を消したいときは、さらにネストを重ねます。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), "株", "")

ネストが深くなると読みにくくなります。3段以上になる場合は、後述するREGEXREPLACE関数のほうがすっきり書けますよ。

第4引数で「何番目だけ」を指定する方法

同じ文字が複数ある場合の挙動

第4引数を省略するとすべて置換されます。しかし「1番目だけ」「3番目だけ」を置き換えたい場合もありますよね。

A2に「りんご-みかん-りんご-ぶどう」が入っています。

=SUBSTITUTE(A2, "りんご", "バナナ")     → 「バナナ-みかん-バナナ-ぶどう」
=SUBSTITUTE(A2, "りんご", "バナナ", 1)  → 「バナナ-みかん-りんご-ぶどう」
=SUBSTITUTE(A2, "りんご", "バナナ", 2)  → 「りんご-みかん-バナナ-ぶどう」
_images/spreadsheet-substitute-function/06_result_substitute-nth.png

第4引数に「1」を指定すると、最初の「りんご」だけが置き換わります。「2」なら2番目だけです。

実務活用例

商品コードが「A-001-A-B」のように複数のハイフンで区切られているとします。2番目のハイフンだけをスラッシュに変えたい場合です。

=SUBSTITUTE(A2, "-", "/", 2)  → 「A-001/A-B」

全体を変えずにピンポイントで置換できるのが第4引数の強みです。使う場面は限られますが、覚えておくと便利ですよ。

SUBSTITUTE・REPLACE・REGEXREPLACEの使い分け

スプレッドシートには文字列を置換する関数が3つあります。それぞれ「何を基準に置き換えるか」が違います。

3関数の比較表

項目SUBSTITUTEREPLACEREGEXREPLACE
置換の指定方法文字列検索（何という文字か）位置指定（何文字目から何文字分）正規表現パターン
大文字/小文字区別する位置指定のため無関係フラグで制御可能
複数箇所の置換省略で全置換1か所のみパターン一致で全置換
複数種類の文字ネストが必要不可1つの正規表現で対応可能
難易度低い低い正規表現の知識が必要

使い分けの判断基準はシンプルです。

  • 特定の文字列を置き換えたい → SUBSTITUTE
  • 特定の位置の文字を置き換えたい → REPLACE
  • パターンで柔軟に置き換えたい → REGEXREPLACE

たとえば「3文字目から2文字を消す」ならREPLACEです。「ハイフンを全部消す」ならSUBSTITUTEです。「数字だけ消す」「カッコと中身をまとめて消す」のようにパターンで指定したいならREGEXREPLACEの出番です。

実務で一番使うのはSUBSTITUTEです。まずはSUBSTITUTEを覚えてください。対応できない場面が出てきたら、REPLACEやREGEXREPLACEを検討してみましょう。

よくある失敗と対処法

大文字・小文字の区別に注意

SUBSTITUTE関数は大文字と小文字を区別します。これはFIND関数と同じ仕様です。

=SUBSTITUTE("ABC", "a", "x")  → 「ABC」（変化なし）
=SUBSTITUTE("ABC", "A", "x")  → 「xBC」

小文字の「a」を検索しても、大文字の「A」にはヒットしません。エラーにはなりません。元テキストがそのまま返るだけなので、「置換できていない」事故に気づきにくいのです。

大文字小文字を無視して置換したいときは、LOWERやUPPERで統一してから使います。

=SUBSTITUTE(LOWER(A2), "abc", "xyz")

ただしこの方法だと出力結果もすべて小文字になります。元の大文字小文字を保持したい場合はREGEXREPLACE関数の(?i)フラグを検討してください。

全角・半角の罠

もうひとつよくあるのが、全角と半角の違いに気づかないケースです。

=SUBSTITUTE(A2, " ", "")   → 半角スペースだけ削除
=SUBSTITUTE(A2, "　", "")  → 全角スペースだけ削除

半角スペースと全角スペースは別の文字として扱われます。見た目がほぼ同じなので気づきにくいのがやっかいです。

「スペースを消したはずなのに残っている」と思ったら、全角・半角の混在を疑ってください。両方消したい場合はネストで対応します。

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

ハイフンも同様です。半角「-」と全角「ー」は別物なので注意してくださいね。

まとめ

SUBSTITUTE関数の要点を整理します。

ポイント内容
基本構文=SUBSTITUTE(対象, 検索文字列, 置換文字列, [出現回数])
全置換第4引数を省略するとすべて置換
文字の削除置換文字列に空文字（””）を指定
大文字/小文字区別する（LOWERで回避可能）
全角/半角区別する（ネストで両対応）
見つからない場合エラーにならず元テキストを返す
複数種類の置換ネストで対応（3種以上はREGEXREPLACE推奨）

SUBSTITUTE関数は「この文字を別の文字に変えたい」というシンプルな要望に応える関数です。電話番号のハイフン削除やスペースの除去など、日常的なデータ整理で大活躍します。

まずはハイフンやスペースの削除から試してみてください。慣れてきたらネストで複数文字の一括置換に挑戦して、データクリーニングの幅を広げていきましょう。

スポンサーリンク
タイトルとURLをコピーしました