セルの中に混ざったハイフンやスペースを一括で取り除きたい。そんなとき、手作業で一つずつ修正するのは大変ですよね。SUBSTITUTE関数を使えば、指定した文字列を別の文字列にまとめて置き換えられます。この記事では基本の書き方から実務で使える置換パターンまで、まとめて紹介します。
この記事は次のような人におすすめ
- セルの中の特定の文字を別の文字に一括で置き換えたい
- 電話番号のハイフンや余分なスペースをまとめて除去したい
- SUBSTITUTE関数とREPLACE関数の違いが知りたい
SUBSTITUTE関数とは？
SUBSTITUTE（サブスティチュート）関数は、文字列の中から指定した文字を検索し、別の文字に置き換える関数です。英語の「substitute」は「代わりに置く」という意味で、まさに文字列の差し替え専用の関数です。
たとえば「Excel」という文字を「エクセル」に置き換えたり、電話番号の「-」を消して数字だけにしたりできます。Excelの「検索と置換」機能（Ctrl+H）と似ていますが、SUBSTITUTE関数は数式なので元のデータを変更しません。別のセルに結果を出力するので安心です。
SUBSTITUTE関数の書き方（構文と引数）
基本構文
=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])引数の説明
| 引数 | 必須/省略可 | 説明 |
|---|---|---|
| 文字列 | 必須 | 置換対象のテキスト、またはセル参照 |
| 検索文字列 | 必須 | 置き換えたい文字列（大文字・小文字を区別する） |
| 置換文字列 | 必須 | 置き換え後の文字列（””を指定すると削除になる） |
| 置換対象 | 省略可 | 何番目に見つかった検索文字列だけを置換するか指定（省略するとすべて置換） |
NOTE
第4引数の「置換対象」は省略するケースがほとんどです。特定の出現箇所だけを変えたいときに数値で指定します。たとえば「2」を指定すると、2番目に見つかった文字列だけが置き換わります。
SUBSTITUTE関数の基本的な使い方
セル参照で文字列を置換する
セルA1に「Excelは事務職に必須のツールです」と入っている場合に、「Excel」を「エクセル」に置き換えてみます。
=SUBSTITUTE(A1,"Excel","エクセル")結果は「エクセルは事務職に必須のツールです」になります。元のセルA1の値はそのまま残るので安心です。
文字列を削除する（空文字で置換）
置換文字列に「””」（空文字）を指定すると、該当する文字を削除できます。
=SUBSTITUTE(A1,"-","")A1が「03-1234-5678」なら、結果は「0312345678」です。ハイフンをすべて消せるので、電話番号の整形によく使われます。
第4引数で特定の位置だけ置換する
同じ文字列が複数回登場する場合に、指定した順番の文字だけを置き換えられます。
=SUBSTITUTE("りんご・みかん・りんご","りんご","バナナ",2)結果は「りんご・みかん・バナナ」です。2番目の「りんご」だけが「バナナ」に変わります。第4引数を省略するとすべての「りんご」が置換されます。
SUBSTITUTE関数の実務活用パターン
ハイフン・スペースの除去（データ整形）
CSVや外部システムから取り込んだデータには、余分なハイフンやスペースが混ざりがちです。SUBSTITUTE関数で一括除去できます。
=SUBSTITUTE(A1,"-","")郵便番号の「123-4567」を「1234567」にしたり、電話番号のハイフンを消したりする場面で使えます。半角スペースを除去したい場合は次のように書きます。
=SUBSTITUTE(A1," ","")TIP
前後の空白だけ削除したい場合はTRIM関数が便利です。SUBSTITUTE関数は文字列内の全スペースを消す点が異なります。
改行の削除（CHAR関数との組み合わせ）
セル内改行は目に見えない文字（文字コード10）なので、直接入力できません。CHAR関数と組み合わせて削除します。
=SUBSTITUTE(A1,CHAR(10),"")Alt+Enterで入力したセル内改行がすべて消えます。改行以外の印刷できない文字も消したい場合は、CLEAN関数と併用するのがおすすめです。
=CLEAN(SUBSTITUTE(A1,CHAR(10)," "))改行を半角スペースに置き換えたうえで、残りの制御文字もCLEAN関数で除去するパターンです。
SUBSTITUTE関数のネスト（複数文字の一括置換）
SUBSTITUTE関数を入れ子にすると、複数の文字をまとめて置換できます。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-","")," ","")," ","")この数式は、ハイフン → 半角スペース → 全角スペースの順番に3つの文字を一括で削除します。入れ子が深くなると読みにくくなりますが、やっていることはシンプルです。外側のSUBSTITUTEから順に「前の結果を次のSUBSTITUTEに渡す」だけです。
TIP
全角・半角の変換にはASC関数が使えます。SUBSTITUTEで個別に全角・半角を消すより効率的な場合があります。
特定の文字を別の文字に変換する
表記ゆれを統一する場面でも活躍します。
=SUBSTITUTE(A1,"株式会社","(株)")住所録や取引先リストで「株式会社」を「(株)」に統一したいときにまとめて変換できます。
SUBSTITUTE関数とREPLACE関数の違い
文字列を置き換える関数にはREPLACE関数もあります。名前が似ているので混同しやすいですが、使い分けのポイントは「置き換える対象の指定方法」です。
| 項目 | SUBSTITUTE関数 | REPLACE関数 |
|---|---|---|
| 置換対象の指定 | 文字列で指定 | 位置（何文字目）で指定 |
| 構文 | =SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象]) | =REPLACE(文字列, 開始位置, 文字数, 置換文字列) |
| 得意な場面 | 特定の文字を探して置き換える | 決まった位置の文字を置き換える |
| 使用例 | ハイフン除去・表記統一 | 先頭3文字を伏字にする・電話番号の市外局番だけ変更 |
| 大文字・小文字 | 区別する | 関係なし（位置で指定するため） |
「この文字を消したい・変えたい」ならSUBSTITUTE関数です。「何文字目から何文字を変えたい」ならREPLACE関数と覚えておくとスムーズです。
なお、文字列の位置を取得する関数としてはLEN関数やMID関数も便利です。
よくあるエラーと対処法
SUBSTITUTE関数はエラーが出にくい関数ですが、意図した結果にならないケースがあります。
| 症状 | 原因 | 対処法 |
|---|---|---|
| 置換されない | 大文字・小文字が一致していない | SUBSTITUTE関数は大文字と小文字を区別します。「excel」と「Excel」は別の文字列として扱われるため、正確に入力してください |
| 置換されない | 見えない文字（空白・改行）が含まれている | TRIM関数やCLEAN関数で不要な文字を除去してから置換してください |
| 全角・半角の不一致で置換されない | 検索文字列と実際の文字の全角半角が異なる | ASC関数で半角に統一してからSUBSTITUTE関数を適用すると確実です |
| #VALUE!エラー | 引数が不足している | 第1〜第3引数はすべて必須です。省略するとエラーになります |
| 意図しない箇所も置換される | 検索文字列が部分一致する | 「市」を置換すると「市区町村」の「市」も変わります。検索文字列をより具体的に指定してください |
NOTE
SUBSTITUTE関数の大文字・小文字の区別は、英字だけでなく全角英字にも適用されます。「Ａ」と「ａ」も別の文字として扱われるので注意してください。
まとめ
SUBSTITUTE関数は「セルの中の特定の文字を別の文字に置き換える」ための関数です。
- 第3引数に「””」を指定すると、文字の削除として使える
- 第4引数で「n番目だけ置換」が可能
- ネスト（入れ子）にすれば複数の文字を一括置換できる
- REPLACE関数との違いは「文字で探すか、位置で探すか」
ハイフン除去・改行削除・表記統一など、実務のデータ整形で幅広く使える関数です。ぜひ活用してみてください。
関連記事
関数一覧
Excel関数の一覧は下記の記事で確認できます。
エラー値が表示される場合は、下記の記事も参考にしてみてください。
