ExcelのSUBSTITUTE関数完全ガイド：基本から応用まで解説

2024 8/25

2024年8月25日

ExcelのSUBSTITUTE関数とは？基本的な使い方を解説

Excelで文字列を置換する際に便利なSUBSTITUTE関数。「置換機能でできるならSUBSTITUTE関数は必要ない？」そんな疑問をお持ちの方も多いのではないでしょうか。本記事では、SUBSTITUTE関数の基本的な使い方から応用テクニックまで、分かりやすく解説します。

こんなお悩みはありませんか？

大量のデータ内の特定の文字列を一括で置換したい
条件に応じて動的に文字列を置換する方法が分からない
置換機能とSUBSTITUTE関数の違いが分からない

本記事を読めば、これらの悩みを解決し、効率的なデータ処理が可能になります。SUBSTITUTE関数をマスターして、Excelスキルを一段階上げましょう！

SUBSTITUTE関数とは

SUBSTITUTE関数は、指定した文字列内の特定の文字や文字列を別の文字列に置き換える関数です。基本的な構文は以下の通りです：

=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])

各引数の意味は次の通りです：

文字列：置換を行う対象となる元の文字列
検索文字列：置換したい元の文字列
置換文字列：新しく置き換える文字列
置換対象：（省略可能）何番目の出現を置換するかを指定する数値

SUBSTITUTE関数の特徴は、セル内の文字列を動的に変更できることです。これにより、条件に応じて自動的に文字列を置換するような高度な処理が可能になります。

置換機能とSUBSTITUTE関数の違い

ExcelにはSUBSTITUTE関数の他に、「置換」機能も用意されています。両者には似た役割がありますが、使用場面や特徴に違いがあります。

機能	置換機能	SUBSTITUTE関数
操作方法	GUI操作（Ctrl+H）	関数入力
置換範囲	選択範囲内のすべてのセル	関数を入力したセルのみ
動的更新	不可	可能（参照元が変更されると自動更新）
条件付き置換	不可	可能（他の関数と組み合わせ可能）

置換機能は広範囲のデータを一括で置換する際に便利ですが、SUBSTITUTE関数は条件に応じた動的な置換や、他の関数と組み合わせた複雑な処理に適しています。

SUBSTITUTE関数の基本的な使い方

SUBSTITUTE関数の基本的な使い方を、具体例を交えて解説します。

例1：単純な文字列置換

セルA1に「おさとエクセル」という文字列が入っているとします。これを「ユースフルエクセル」に変更する場合、次のように関数を使用します：

=SUBSTITUTE(A1, "おさと", "ユースフル")

この関数は、A1セル内の「おさと」という文字列を「ユースフル」に置換します。

例2：複数回出現する文字列の置換

セルB1に「おさのエクセル講座はおさが教えます」という文字列があるとします。すべての「おさ」を「ユースフル」に置換する場合：

=SUBSTITUTE(B1, "おさ", "ユースフル")

この関数は、B1セル内のすべての「おさ」を「ユースフル」に置換します。結果は「ユースフルのエクセル講座はユースフルが教えます」となります。

例3：特定の出現回数のみを置換

同じB1セルの内容で、最初の「おさ」のみを置換したい場合は、4つ目の引数を使用します：

=SUBSTITUTE(B1, "おさ", "ユースフル", 1)

この関数は、B1セル内の1番目の「おさ」のみを「ユースフル」に置換します。結果は「ユースフルのエクセル講座はおさが教えます」となります。

SUBSTITUTE関数の応用テクニック

SUBSTITUTE関数は他の関数と組み合わせることで、より高度なデータ処理が可能になります。ここでは、いくつかの応用例を紹介します。

複数の文字列を連続して置換する

複数のSUBSTITUTE関数を入れ子にすることで、一度に複数の文字列を置換できます。

=SUBSTITUTE(SUBSTITUTE(A1, "古い", "新しい"), "悪い", "良い")

この関数は、A1セル内の「古い」を「新しい」に、「悪い」を「良い」に連続して置換します。

条件付きで置換を行う

IF関数と組み合わせることで、条件に応じて置換を行うことができます。

=IF(A1="要置換", SUBSTITUTE(B1, "古い", "新しい"), B1)

この関数は、A1セルが「要置換」の場合のみ、B1セル内の「古い」を「新しい」に置換します。それ以外の場合は、B1セルの内容をそのまま返します。

文字列の一部を抽出する

SUBSTITUTE関数を使って、文字列の一部を抽出することも可能です。

=SUBSTITUTE(A1, LEFT(A1, FIND(",", A1)), "")

この関数は、A1セル内のカンマより前の部分を削除します。例えば、A1が「姓,名」という形式の場合、カンマの後ろの「名」のみを抽出できます。

SUBSTITUTE関数使用時の注意点

SUBSTITUTE関数を使用する際に、以下のような間違いに注意しましょう：

大文字と小文字を区別しない
SUBSTITUTE関数は大文字と小文字を区別します。「Excel」を置換対象にしても、「EXCEL」や「excel」は置換されません。この問題を回避するには、UPPER関数やLOWER関数と組み合わせて使用する方法があります。
全角と半角を区別しない
全角の「Ａ」と半角の「A」は別の文字として扱われます。意図した置換が行われない場合は、全角・半角の違いを確認してください。
空白文字の扱いを誤る
目に見えない空白文字が含まれていると、意図した置換が行われないことがあります。TRIM関数と組み合わせて使用するなど、空白文字の処理に注意が必要です。
置換後の文字列長を考慮しない
置換後の文字列が元の文字列よりも長くなる場合、セルの表示が「###」になることがあります。列幅の調整や、TEXTWRAP関数の使用を検討してください。

これらの注意点を把握し、適切に対処することで、SUBSTITUTE関数をより効果的に活用できます。

SUBSTITUTE関数の練習問題

理解を深めるために、いくつかの練習問題に挑戦してみましょう。

問題1

セルA1に「The quick brown fox jumps over the lazy dog」という文字列があります。この文中の「the」をすべて「a」に置換する関数を作成してください。

回答

=SUBSTITUTE(A1, "the", "a")

解説

この関数は、大文字と小文字を区別するため、「The」は置換されず「the」のみが「a」に置換されます。結果は「The quick brown fox jumps over a lazy dog」となります。

問題2

セルB1に「123-456-789」という文字列があります。この文字列からハイフン（-）を削除する関数を作成してください。

回答

=SUBSTITUTE(B1, "-", "")

解説

この関数は、B1セル内のすべてのハイフンを空文字に置換します。結果は「123456789」となります。

問題3

セルC1に「ExcelはMicrosoftのExcelです」という文字列があります。2番目の「Excel」のみを「スプレッドシート」に置換する関数を作成してください。

回答

=SUBSTITUTE(C1, "Excel", "スプレッドシート", 2)

解説

この関数は、4つ目の引数に2を指定することで、2番目に出現する「Excel」のみを置換します。結果は「ExcelはMicrosoftのスプレッドシートです」となります。

よくある質問（FAQ）

SUBSTITUTE関数に関するよくある質問とその回答をまとめました。

Q1: SUBSTITUTE関数と置換機能はどちらを使うべきですか？

A1: 状況によって使い分けるのが最適です。単純な一括置換なら置換機能、条件付き置換や動的な更新が必要な場合はSUBSTITUTE関数が適しています。また、マクロやVBAでの利用を考慮する場合も、SUBSTITUTE関数の方が柔軟に対応できます。

Q2: SUBSTITUTE関数で大文字と小文字を区別せずに置換するには？

A2: UPPER関数やLOWER関数と組み合わせることで、大文字小文字を区別せずに置換できます。例えば：

=SUBSTITUTE(UPPER(A1), UPPER("検索"), UPPER("置換"))

この方法では、元の文字列、検索文字列、置換文字列をすべて大文字に変換してから置換を行います。

Q3: SUBSTITUTE関数で複数の文字列を一度に置換できますか？

A3: 複数のSUBSTITUTE関数を入れ子にすることで、複数の文字列を連続して置換できます。例えば：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "古い1", "新しい1"), "古い2", "新しい2"), "古い3", "新しい3")

この方法で、「古い1」「古い2」「古い3」をそれぞれ「新しい1」「新しい2」「新しい3」に置換できます。

Q4: SUBSTITUTE関数の結果が#VALUE!エラーになる原因は？

A4: #VALUE!エラーの主な原因は以下の通りです：

文字列引数に数値を使用している
置換対象引数に文字列や負の数を指定している
関数の構文が正しくない（カンマの位置など）

引数の型と構文を確認し、適切な値が入力されているか確認してください。

Q5: SUBSTITUTE関数で正規表現は使えますか？

A5: SUBSTITUTE関数自体は正規表現をサポートしていません。正規表現を使用したい場合は、VBAを利用するか、Power QueryのM言語を使用する必要があります。ただし、一部の簡単なパターンマッチングであれば、SUBSTITUTE関数とワイルドカードを使用するLIKE演算子を組み合わせることで代用できる場合もあります。

SUBSTITUTE関数と関連する他の関数

SUBSTITUTE関数と組み合わせて使用したり、類似した機能を持つ関数をいくつか紹介します。これらの関数を適切に活用することで、より効率的なデータ処理が可能になります。

1. REPLACE関数

REPLACE関数は、指定した位置から指定した文字数を別の文字列に置き換えます。SUBSTITUTE関数が特定の文字列を検索して置換するのに対し、REPLACE関数は位置を指定して置換を行います。

=REPLACE(元の文字列, 開始位置, 文字数, 新しい文字列)

2. FIND関数とMID関数

FIND関数で特定の文字列の位置を見つけ、MID関数で部分文字列を抽出することで、SUBSTITUTE関数と似たような操作ができます。

=MID(A1, FIND("検索", A1), LEN("検索"))

3. TRIM関数

TRIM関数は文字列の前後や単語間の余分な空白を削除します。SUBSTITUTE関数と組み合わせて使用することで、より正確な文字列操作が可能になります。

=SUBSTITUTE(TRIM(A1), " ", "")

4. CONCATENATE関数（または&演算子）

文字列の結合に使用するCONCATENATE関数や&演算子は、SUBSTITUTE関数と組み合わせて複雑な文字列操作を行う際に便利です。

=CONCATENATE(LEFT(A1, FIND(",", A1)-1), " ", SUBSTITUTE(RIGHT(A1, LEN(A1)-FIND(",", A1)), ",", ""))

5. IFERROR関数

IFERROR関数をSUBSTITUTE関数と組み合わせることで、エラーが発生した場合の処理を指定できます。

=IFERROR(SUBSTITUTE(A1, "検索", "置換"), "エラーが発生しました")

これらの関数を適切に組み合わせることで、より柔軟で高度な文字列処理が可能になります。実際の業務でどのような文字列操作が必要になるかを考慮し、最適な関数の組み合わせを選択することが重要です。

SUBSTITUTE関数の実践的な使用例

SUBSTITUTE関数の理解をさらに深めるため、実務でよく遭遇する場面での使用例を紹介します。

例1: メールアドレスのドメイン変更

会社のドメインが変更になり、すべての社員のメールアドレスを一括で更新する必要がある場合：

=SUBSTITUTE(A1, "@old-domain.com", "@new-domain.com")

この関数により、A1セルのメールアドレスの「@old-domain.com」部分が「@new-domain.com」に置換されます。

例2: 電話番号のフォーマット統一

異なる形式で入力された電話番号を統一フォーマットに変更する場合：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-", ""), " ", ""), ")", "-")

この関数は、A1セルの電話番号からハイフン、スペース、閉じカッコを削除し、閉じカッコの位置にハイフンを挿入します。例えば、「(03) 1234-5678」は「03-12345678」に変換されます。

例3: CSV形式のデータクリーニング

CSVファイルから取り込んだデータのカンマの前後の余分なスペースを削除する場合：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " ,", ","), ", ", ","), ",,", ",")

この関数は、カンマの前後のスペースを削除し、さらに連続したカンマを1つに置換します。データの整合性を保ちながらクリーニングができます。

例4: URLのプロトコル統一

Webサイトの移行で、すべてのURLをhttpsに統一する必要がある場合：

=IF(LEFT(A1,4)="http", SUBSTITUTE(A1, "http:", "https:", 1), A1)

この関数は、A1セルのURLが「http」で始まる場合のみ、「http:」を「https:」に置換します。既にhttpsのURLは変更されません。

例5: 商品コードの世代更新

古い商品コードの一部を新しい世代コードに更新する場合：

=SUBSTITUTE(A1, "OLD", "NEW", 1)

この関数は、A1セルの商品コードの最初の「OLD」という文字列のみを「NEW」に置換します。例えば「OLD-1234-OLD」は「NEW-1234-OLD」になります。

これらの実践的な例を参考に、自身の業務に合わせてSUBSTITUTE関数を活用してみてください。関数の特性を理解し、適切に使用することで、データ処理の効率を大幅に向上させることができます。

SUBSTITUTE関数を使いこなすためのTips＆Tricks

SUBSTITUTE関数をより効果的に使用するための、いくつかのヒントとテクニックを紹介します。

1. 複数のSUBSTITUTE関数の使用順序に注意する

複数のSUBSTITUTE関数を入れ子にして使用する場合、その順序が重要です。例えば：

=SUBSTITUTE(SUBSTITUTE(A1, "Excel", "表計算ソフト"), "excel", "EXCEL")

この場合、最初の置換で「Excel」が「表計算ソフト」に変わるため、2番目の置換は効果がありません。順序を逆にすることで、意図した結果が得られます。

2. SUBSTITUTE関数と数値計算を組み合わせる

SUBSTITUTE関数は文字列を返しますが、数値に変換することで計算に利用できます。例えば：

=VALUE(SUBSTITUTE(A1, "$", "")) * 1.1

この関数は、A1セルの金額から「$」記号を削除し、その値を10%増加させます。

3. SUBSTITUTE関数でセル参照を動的に変更する

SUBSTITUTE関数を使って、数式内のセル参照を動的に変更することができます。例えば：

=INDIRECT(SUBSTITUTE(ADDRESS(ROW(),COLUMN()), "$", ""))

この関数は、現在のセルの参照を絶対参照から相対参照に変更します。

4. SUBSTITUTE関数で条件付き書式のルールを作成する

条件付き書式のルールにSUBSTITUTE関数を使用することで、より柔軟な条件設定が可能になります。例えば：

=LEN(A1) < > LEN(SUBSTITUTE(A1, " ", ""))

この条件は、A1セルに空白が含まれている場合にtrueを返します。

5. SUBSTITUTE関数でデータの匿名化を行う

個人情報などのセンシティブなデータを匿名化する際に、SUBSTITUTE関数が役立ちます。例えば：

=SUBSTITUTE(A1, MID(A1, 2, LEN(A1)-2), REPT("*", LEN(A1)-2))

この関数は、A1セルの文字列の最初と最後の文字を除いて、すべての文字を「*」に置換します。

これらのTips＆Tricksを活用することで、SUBSTITUTE関数の可能性を最大限に引き出し、より効率的で創造的なExcel作業が可能になります。実際の業務シーンで試してみて、自分なりの活用方法を見つけてみてください。

まとめ：SUBSTITUTE関数を使いこなそう

SUBSTITUTE関数は、Excelでのデータ処理や文字列操作に欠かせない強力なツールです。基本的な使い方から応用テクニックまで、本記事で解説した内容を実践することで、より効率的なExcel作業が可能になるでしょう。

SUBSTITUTE関数の主なポイントを振り返ってみましょう：

文字列内の特定の部分を別の文字列に置換できる
置換機能と比べて、動的な更新や条件付き置換が可能
他の関数と組み合わせることで、複雑なデータ処理に対応できる
大文字・小文字の区別や空白文字の扱いに注意が必要

SUBSTITUTE関数をマスターすることで、データクレンジングやテキスト分析など、さまざまな場面でExcelの真価を発揮できるようになります。ぜひ、日々の業務や個人的なデータ管理に活用してみてください。

より高度なExcelスキルを身につけたい方は、ユースフルのExcelPro講座をおすすめします。基本から応用まで、実践的なスキルを効率的に学ぶことができます。

無料トライアルに登録する

気に入ったらシェア！

URLをコピーしました！