「住所の“−”を全角から半角に統一したい」
「電話番号のハイフンを消して数値化したい」
「商品名の旧ブランド名を一括で変更したい」
そんな “テキストの置換・クリーニング” を一瞬で実現するのが SUBSTITUTE 関数。
本記事では 実データ＋数式＋出力結果 を示しながら、
基本から応用まで“すぐ業務で使える”テクニックを解説します。
1. SUBSTITUTE関数 ── 文字列の一部を別文字に置換
=SUBSTITUTE(文字列, 置換前, 置換後, [出現番号])
| 引数 | 内容 | 既定値 |
|---|
| 文字列 | 変換対象（セル参照可） | 必須 |
| 置換前 | 置換したい文字列 | 必須 |
| 置換後 | 置換後の文字列（空文字 “” も可） | 必須 |
| 出現番号 | 何番目の出現だけ置換するか（省略で“すべて”） | 任意 |
ポイント
- 大文字小文字は区別（必要なら
LOWER/UPPER 併用）
- 複数文字列の一括置換は ネスト か REGEXREPLACE と併用
2. 基本例①：全角ハイフンを半角に
| A列：住所 (入力元) |
|---|
| 東京都－新宿区 |
| 大阪府－大阪市 |
=SUBSTITUTE(A2,"－","-")
3. 基本例②：電話番号ハイフンを除去
| A列：電話番号 |
|---|
| 03-1234-5678 |
| 06-9876-5432 |
=SUBSTITUTE(A2,"-","")
活用： 数値化 → TEXT関数で書式設定 すると計算・検索が安定。
4. 応用例：旧ブランド名を新ブランド名へ一括置換
| A列：商品名 (旧表記) |
|---|
| 【XYZ】スマートウォッチ |
| 【XYZ】ワイヤレスイヤホン |
| 【XYZ】Bluetoothスピーカー |
=SUBSTITUTE(A2,"XYZ","Nova")
| 出力 |
|---|
| 【Nova】スマートウォッチ |
| 【Nova】ワイヤレスイヤホン |
| 【Nova】Bluetoothスピーカー |
5. 特定回だけ置換したい（第 4 引数）
| A列：型番 |
|---|
| PRD-2024-001 |
| PRD-2024-002 |
末尾の “-” だけ “/” に変えたい場合
=SUBSTITUTE(A2,"-","/",3)
| 出力 |
|---|
| PRD-2024/001 |
| PRD-2024/002 |
6. 複数文字列をまとめて置換するテクニック
■ スペース・カンマ・スラッシュ → 空白 1 個に統一
=SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE( A2 , "/" , " " ) ,
"," , " " ) ,
" " , " " )
- ネスト で順番に置換
- 最後に
" "（二重スペース）→ " " で連続空白を 1 つに
または
=REGEXREPLACE(A2,"[,/ ]+"," ")
7. 仕事で役立つ活用シーン
| シーン | 使い方イメージ | 効果 |
|---|
| 営業：顧客リスト電話番号整形 | =SUBSTITUTE(番号,"-","") | 電話番号検索・Dialer連携が安定 |
| マーケ：UTMパラメータ削除 | =SUBSTITUTE(URL,"?utm_source="&B2,"") | クリーン URL を解析用に生成 |
| 経理：請求書No. 書式統一 | "INV-"&SUBSTITUTE(番号,"/","") | システム取込時のエラー防止 |
| 開発：CSV→マスタ取込 | `=SUBSTITUTE(テキスト,CHAR(10),” | “)` |
| 人事：氏名の「 」（全角空白）削除 | =SUBSTITUTE(氏名," "," ") | 姓名分割前のクリーニング |
8. よくあるエラー & 回避策
| 症状 | 原因 | 対処 |
|---|
| 置換後に空白が増える | 連続スペースで複数スプリット | SUBSTITUTE( …," "," ") を追加 |
| 大文字だけ置換されない | ケースが混在 | =SUBSTITUTE(UPPER(A2),"OLD","NEW") + PROPER で整形 |
| 複数区切り文字 | 一括は不可 | ネスト or REGEXREPLACE 併用 |
9. まとめ & 早見表
| 目的 | 代表式 | キーワード |
|---|
| ハイフン全削除 | =SUBSTITUTE(A2,"-","") | 番号整形 |
| 全角→半角 | =SUBSTITUTE(A2,"－","-") | 表記統一 |
| ブランド名変更 | =SUBSTITUTE(A2,"XYZ","Nova") | 名称再掲 |
| 第 n 出現だけ置換 | =SUBSTITUTE(A2,"-","/",3) | 位置指定 |
| 複数文字列置換 | =REGEXREPLACE(A2,"[,/ ]+"," ") | 混在データ掃除 |
SUBSTITUTE で“表記ゆれ”とサヨナラ！
データベース連携・VLOOKUP・集計の精度が劇的に向上します。
ぜひ明日からのスプレッドシート業務に取り入れ、
テキスト整形の手間を大幅短縮しましょう。
