文字列を置換したいSUBSTITUTE関数-GSS/Excel/関数
monroe文字列を置換したいSUBSTITUTE関数-GSS/Excel/関数
文字列の置換を行うSUBSTITUTE関数
データ整理をしていると、「全角文字を半角に統一したい」「カンマをピリオドに変更したい」「不要な文字を削除したい」など、文字列の置換作業が必要になることってありますよね。手作業で一つ一つ修正するのは時間がかかりすぎます。
今回はSUBSTITUTE関数をご紹介します。SUBSTITUTE関数は文字列内の特定の文字や文字列を別の文字列に一括で置換してくれる便利な関数です。データクレンジングの現場では欠かせない関数で、作業効率を大幅に向上させてくれます。
【サンプルデータの表示画像】
A列に顧客名（全角・半角混在）、B列に電話番号（ハイフン有り）、C列に住所（表記ゆれ有り）が入力されたスプレッドシートの例
今回のサンプルデータでは、A列に顧客名（全角・半角が混在）、B列に電話番号（ハイフン有り）、C列に住所（表記ゆれ有り）が入力されています。これらのデータを統一した形式に整える作業を通してSUBSTITUTE関数を学習していきましょう。
SUBSTITUTE関数の構文
パラメータの詳細は以下の通りです：
- 対象文字列：置換を行う元の文字列
- 検索文字列：置換したい文字または文字列
- 置換文字列：置換後の文字または文字列
- 置換対象番号：何番目の該当文字を置換するか（省略可能）
基本的な使用例
具体的な例を見てみましょう。電話番号「090-1234-5678」からハイフンを削除する場合：
| セル | 内容 | 説明 |
|---|---|---|
| A1 | 090-1234-5678 | 元の電話番号 |
| B1 | =SUBSTITUTE(A1, "-", "") | ハイフンを空文字に置換 |
| 結果 | 09012345678 | ハイフンが削除された状態 |
この関数は「09012345678」を返します。検索文字列「-」を置換文字列「""」（空文字）に置換することで、実質的にハイフンを削除しています。
実践的な活用例（絶対参照の活用）
実際の業務では、複数のデータを一括で処理することが多いです。置換文字列を別のセルで管理することで、メンテナンスしやすい関数を作ることができます：
| セル | 内容 | 説明 |
|---|---|---|
| D1 | 株式会社 | 検索文字列 |
| E1 | (株) | 置換文字列 |
| B2 | =SUBSTITUTE(A2, $D$1, $E$1) | 絶対参照を使った置換 |
【実践例の表示画像】
A列に「○○株式会社」、B列に=SUBSTITUTE(A2,$D$1,$E$1)が入力され、結果として「○○(株)」が表示されている例
置換対象番号を使った部分置換
同じ文字が複数回出現する場合、4番目のパラメータ「置換対象番号」を使って特定の位置のみを置換できます。例えば、「apple-orange-apple-grape」の2番目の「apple」のみを「melon」に置換する場合：
結果：「apple-orange-melon-grape」
この機能は、データの特定部分のみを修正したい場合に非常に有効です。住所データで「○○市○○区」の最初の「○○」のみを置換するような場面で活躍します。
複数の置換を組み合わせる高度な活用
SUBSTITUTE関数を入れ子（ネスト）にすることで、複数の置換を一度に実行できます。例えば、文字列から複数の不要な文字を一括削除する場合：
この例では、括弧とスペースを段階的に削除しています：
- 最初のSUBSTITUTE：「(」を削除
- 2番目のSUBSTITUTE：「)」を削除
- 3番目のSUBSTITUTE：スペースを削除
| 処理段階 | 元の文字列 | 処理後 |
|---|---|---|
| 元データ | 山田 太郎 (営業部) | - |
| 1段階目 | 山田 太郎 (営業部) | 山田 太郎 営業部) |
| 2段階目 | 山田 太郎 営業部) | 山田 太郎 営業部 |
| 3段階目 | 山田 太郎 営業部 | 山田太郎営業部 |
データクレンジングでの実践活用
SUBSTITUTE関数は、データクレンジング（データの清浄化）において特に威力を発揮します。以下のような場面で頻繁に使用されます：
| 用途 | 例 | 関数例 |
|---|---|---|
| 全角→半角変換 | 「１２３」→「123」 | =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"１","1"),"２","2"),"３","3") |
| 区切り文字統一 | 「,」→「.」 | =SUBSTITUTE(A1, ",", ".") |
| 不要文字削除 | 改行文字削除 | =SUBSTITUTE(A1, CHAR(10), "") |
| 表記統一 | 「株式会社」→「(株)」 | =SUBSTITUTE(A1, "株式会社", "(株)") |
【データクレンジング例の表示画像】
Before/Afterの比較表示。整理前の「山田 太郎（営業部）」が整理後「山田太郎営業部」になっている例
効率的な一括処理のテクニック
大量のデータを効率よく処理するために、以下のテクニックを覚えておくと便利です：
1. 置換ルールの外部管理
置換前・置換後の文字列を別の表で管理し、絶対参照で呼び出すことで、ルール変更に柔軟に対応できます。
2. 配列数式の活用
Googleスプレッドシートでは、ARRAYFORMULA関数と組み合わせることで列全体を一度に処理できます：
3. 条件付き置換
IF関数と組み合わせることで、条件に応じた置換も可能です：
まとめ
SUBSTITUTE関数の使い所
- データの表記を統一したいとき
- 不要な文字や記号を一括削除したいとき
- 文字列の形式を変更したいとき（全角→半角など）
- データクレンジング作業を効率化したいとき
構文
実用的な例文
絶対参照活用：=SUBSTITUTE(A1, $D$1, $E$1)
部分置換：=SUBSTITUTE(A1, "apple", "melon", 2)
複数置換：=SUBSTITUTE(SUBSTITUTE(A1, "(", ""), ")", "")
覚えておきたいポイント
- 置換対象番号を省略すると全ての該当文字が置換される
- 絶対参照を活用して置換ルールを外部管理
- ネスト（入れ子）で複数の置換を一度に実行可能
- CHAR関数と組み合わせて特殊文字も処理可能
- 配列数式で大量データの一括処理が可能
SUBSTITUTE関数をマスターすることで、データクレンジング作業が劇的に効率化されます。手作業では数時間かかる作業も、関数を使えば数分で完了します。ぜひ実際のデータで試してみて、データ処理のスキルを向上させてくださいね！
