最終更新日: / 公開日:
最終更新日: / 公開日:
本記事のポイント
- SUBSTITUTE はスプレッドシートで「指定した文字を別の文字に置き換える」関数で、構文は
SUBSTITUTE(対象のセル, 探す文字, 置き換える文字)です。探す文字を空欄""にすれば削除になり、関数を入れ子 (ネスト) にすれば全角スペースと半角スペースのように複数の文字をまとめて処理できます - 氏名の空白除去・電話番号のハイフン除去・「株式会社／㈱」の表記統一・URL のパラメータ除去まで、実務の表記ゆれはほぼ SUBSTITUTE のレシピで機械的に潰せます
- 手作業なら数百行を 1 行ずつ直していた作業が、1 つの式を列にコピーするだけで一瞬で終わります。式は巻末の実行キットに手元のサンプルを渡せば自動で組めます
「リストを HubSpot に取り込む前に、氏名の全角スペースを 1 件ずつ消している」。B2B マーケの現場でよく聞く悩みです。
原因は根気の不足ではありません。表記ゆれは目で直すものではなく、置換関数で機械的に潰すものだからです。
本記事では、その主役である SUBSTITUTE 関数の使い方を、基本構文からネスト・実務レシピ・TRIM や正規表現との使い分けまで、コピーしてすぐ使える式付きで解説します。関数が苦手でも、式は巻末の実行キットで生成できるので、必要なのは「どの表記ゆれを潰したいか」を決める力だけです。
できあがるもの — バラバラの列が 1 式で揃う
完成形から見せます。フォームや名刺データから集めたリストは、たいてい下の「変換前」のように表記が揃っていません。
これを SUBSTITUTE で 1 列ぶん処理すると、右の「変換後」のように揃います。
| 変換前 | 変換後 | 使う処理 |
|---|---|---|
山田 太郎 (間に全角スペース) |
山田太郎 |
スペース除去 |
090-1234-5678 |
09012345678 |
ハイフン除去 |
㈱ヒューマンバルズ |
株式会社ヒューマンバルズ |
表記統一 |
example.com/lp?utm_source=x |
example.com/lp |
パラメータ除去 |
処理はどれも「探す文字を、別の文字 (または空欄) に置き換える」という同じ形です。所要は式を 1 つ書いて列にコピーするだけ、数百行でも一瞬で終わります。以下、STEP 1 から順に組み立てます。
STEP 1. 基本構文 — 3 つの引数を埋めるだけ
「関数と聞くと身構えてしまう」。よく返ってくる反応ですが、SUBSTITUTE は覚える引数が 3 つだけです。
=SUBSTITUTE(対象のセル, 探す文字, 置き換える文字)
たとえば A2 に 株式会社ABC が入っていて、「株式会社」を消したいときはこう書きます。
=SUBSTITUTE(A2, "株式会社", "")
第 3 引数を空欄 "" にすると「探した文字を何にも置き換えない」、つまり削除になります。これが SUBSTITUTE を表記ゆれ潰しの主役にしている一番のポイントです。
つまずきやすいのは、探す文字と置き換える文字を必ず半角ダブルクォート " で囲むことです。
- 囲み忘れると
#ERROR!や#NAME?が返る - 全角スペースを探すときは
" "の中身を全角にするだけ (書き方は同じ)
なお第 4 引数に数字を足すと「何番目に出てきた文字だけ置換するか」を指定できます (SUBSTITUTE(A2," ","",1) で最初の 1 個だけ)。実務では使う場面は多くないので、まずは 3 引数だけ押さえれば十分です。
STEP 2. ネストで複数置換 — 全角と半角の空白を二段で消す
現場のデータで一番やっかいなのが、氏名の空白です。
なぜやっかいかというと、同じ「空白」に見えても、全角スペースと半角スペースは別の文字だからです。 片方だけ消しても、もう片方が残ります。
SUBSTITUTE は 1 回で 1 種類の文字しか消せません。そこで、SUBSTITUTE の結果をもう 1 つの SUBSTITUTE で包む「ネスト (入れ子)」を使います。
=SUBSTITUTE(SUBSTITUTE(A2," ","")," ","")
内側の SUBSTITUTE(A2," ","") がまず半角スペースを消し、その結果を外側の SUBSTITUTE が受け取って全角スペースを消します。内側から外側へ、二段で処理が流れるイメージです。
さらに、姓と名が別々の列 (K 列・L 列) に分かれているなら、それぞれを空白除去してから & で連結すると、1 式で「空白なしのフルネーム」が作れます。
=SUBSTITUTE(SUBSTITUTE(K2," ","")," ","")&SUBSTITUTE(SUBSTITUTE(L2," ","")," ","")
これで 山田 と 太郎 (末尾に全角スペース) が別々の列に入っていても、山田太郎 として 1 セルに揃います。名寄せやキー照合の前処理として、そのまま使える形です。
つまずきポイントは、丸括弧の対応です。ネストが深くなると閉じ括弧の数が合わずエラーになりがちなので、内側の SUBSTITUTE を 1 つ完成させてから外側で包む順に書くと崩れません。3 種類以上の文字を消すなら、さらに SUBSTITUTE を重ねれば同じ要領で対応できます。
STEP 3. 実務レシピ集 — コピーして値だけ差し替える
ここまで来れば、あとは「探す文字」と「置き換える文字」を変えるだけで、たいていの表記ゆれに対応できます。B2B のリスト整備で頻出する 4 パターンを、そのまま使える式にしておきます。
| やりたいこと | 式 | 補足 |
|---|---|---|
| 氏名の空白除去 | =SUBSTITUTE(SUBSTITUTE(A2," ","")," ","") |
半角+全角の二段除去 (STEP 2) |
| 電話番号のハイフン除去 | =SUBSTITUTE(A2,"-","") |
090-1234-5678 → 09012345678 |
| 「㈱」を「株式会社」に統一 | =SUBSTITUTE(A2,"㈱","株式会社") |
逆にしたい場合は引数を入れ替える |
| URL のパラメータ除去 | =SUBSTITUTE(A2,"?utm_source=facebook","") |
? 以降が固定文字列のときに有効 |
電話番号は、ハイフンの種類が混在していることがあります。全角ハイフンや長音符 ー が紛れているなら、STEP 2 のネストで - と ー を二段で消せば揃います。
会社名の表記統一は、㈱ だけでなく (株) （株） も混ざりがちです。これも SUBSTITUTE を重ねて、それぞれを 株式会社 に寄せれば 1 式で片付きます。
つまずきポイントは、SUBSTITUTE が「完全一致した文字列」しか置換しないことです。URL のパラメータのように中身が 1 件ごとに変わる (utm_source の値が違う) 場合は、固定文字列の指定では消しきれません。そのケースは STEP 4 の REGEXREPLACE の出番です。
STEP 4. 使い分け — TRIM と REGEXREPLACE との境界
SUBSTITUTE が万能というわけではありません。似た関数と役割を分けておくと、式が短く済みます。
判断の軸はシンプルです。
- 消したい文字が「決まった 1 種類」なら SUBSTITUTE — ハイフン、特定の全角スペース、
㈱など - 「前後と連続の半角スペースだけ」を消したいなら TRIM —
=TRIM(A2)の 1 発。ただし全角スペースは対象外なので、全角も消すなら SUBSTITUTE と併用する - 「パターン」で消したいなら REGEXREPLACE — 中身が変わる文字列を正規表現でまとめて処理する
たとえば URL のパラメータのように「? 以降をすべて消す」なら、SUBSTITUTE では値ごとに式を書き分けることになります。REGEXREPLACE なら 1 式で済みます。
=REGEXREPLACE(A2, "\?.*$", "")
\?.*$ は「? から行末までのすべて」を表す正規表現で、それを空欄に置き換えています。パターンで消す局面では、SUBSTITUTE を何段も重ねるより REGEXREPLACE のほうが読みやすく保守も楽です。
一方で、正規表現は書き間違えると意図しない文字まで消してしまいます。消す対象が固定文字なら、まず SUBSTITUTE で安全に処理するのが実務の基本です。 正規表現は「固定指定では追いつかないとき」の道具、と切り分けると迷いません。式の書き方は巻末の実行キットで生成できます。
動作確認 — 何が見えれば成功か
式を 1 セルに入れたら、列全体にコピーする前に次の 3 点を確認します。
- 元の値が変わっていないか — SUBSTITUTE は元のセルには手を加えず、別のセルに結果を出します。元列と変換列が並んで見えていれば正常です
- 消したい文字だけが消えているか — 氏名なら空白だけが消え、文字自体は欠けていないか。全角スペースが残っていないかも合わせて確認します
#ERROR!や#NAME?が出ていないか — 出たらダブルクォートの囲み忘れか、丸括弧の対応ずれです。STEP 1・STEP 2 の書式を見直します
3 点が揃えば、あとは式をリストの行数ぶんコピーするだけです。変換列を「値のみ貼り付け」で確定させてから元列を消すと、取り込み用のきれいなリストになります。
まとめ — 表記ゆれは目で直さない
SUBSTITUTE は、探す文字を別の文字に置き換えるだけのシンプルな関数です。空欄に置き換えれば削除になり、ネストすれば全角と半角の空白のように複数の文字をまとめて潰せます。
氏名・電話番号・会社名・URL といった B2B リストの表記ゆれは、STEP 3 のレシピをコピーして値を差し替えるだけで、機械的に揃います。固定文字は SUBSTITUTE、連続する半角スペースは TRIM、パターンは REGEXREPLACE、と切り分ければ式も短く保てます。
私たち humbulls は、こうした地味な前処理こそ AI に式を書かせて、人は「どの表記ゆれを潰すか」の判断に集中すべきだと考えています。
次に進むなら、この 3 本が続きになります。
- 重複行の統合まで含めた名寄せの流れ: スプレッドシートで名寄せする 3 関数
- 整えたリストの取り込み: HubSpot インポートで関連付けまで一括登録する方法
- フォーム流入データを整える運用: Zoom ウェビナー × Google フォームの運用設計
データ整備から HubSpot 運用まで一気通貫で伴走する Growth Partner サービス も提供しています。
🤖 AI 実行キット
本文のレシピを、自分のデータに合わせた式にして受け取るためのキットです。プロンプトは Claude (ブラウザ版で可) にコピペすれば動きます。
キット① 手元の表記ゆれから置換式を組む — 10 分
種別: 実装キット
使うもの: Claude (ブラウザ版で可)
事前に用意するもの: 表記ゆれの「変換前」と「揃えたい変換後」のサンプルを数件 (実データのコピペで可) / 対象セルの列記号 (A 列なら A)
プロンプト:
Google スプレッドシートの SUBSTITUTE 関数を使って、表記ゆれを揃える式を作ってください。
【対象セル】
- 列: A 列（記入例。実際の列記号に置き換え）
- 先頭データ行: 2 行目
【変換前 → 変換後 のサンプル】
- 山田 太郎 → 山田太郎（全角スペースを除去）
- 090-1234-5678 → 09012345678（ハイフンを除去）
- ㈱ABC商事 → 株式会社ABC商事（㈱ を 株式会社 に統一）
（記入例。自社の実データに置き換え。3〜5 件あると精度が上がります）
【必ず守る条件】
1. 固定の 1 文字・文字列を消す／置き換える処理は SUBSTITUTE を使う
2. 全角スペースと半角スペースの両方を消す場合は SUBSTITUTE をネストする
（内側で半角、外側で全角の順）
3. 中身が 1 件ごとに変わるパターン（URL のパラメータ等）は
REGEXREPLACE を提案し、正規表現の意味も 1 行で説明する
4. 完成した式は、そのままセルに貼れる形で 1 行ずつ提示する
【出力】
- サンプルごとに「貼り付ける式」と「何をしているかの 1 行説明」
- 全部を 1 セルにまとめられる場合は、連結した 1 式も併記
出力の確認ポイント:
- 式が
=SUBSTITUTE(...)の形になっていて、探す文字・置き換える文字が半角ダブルクォートで囲まれているか - 全角スペースを消す指定で、クォートの中身がちゃんと全角スペースになっているか (見た目で判別しづらいので、サンプルで動作確認する)
- 元のセルを直接書き換える式になっていないか (SUBSTITUTE は別セルに出力するのが正しい挙動)
うまくいかないとき:
- 式を貼ると
#NAME?が出る → 関数名や引数のクォートが全角になっている可能性。半角で入力し直します - 全角スペースが消えない → ネストの内側と外側で半角・全角を取り違えています。STEP 2 の式と見比べてください
- パラメータが消しきれない → 中身が可変なので SUBSTITUTE では不向きです。「REGEXREPLACE で書き直して」と追加で指示してください
参考文献
- SUBSTITUTE — Google ドキュメント エディタ ヘルプ (取得日: 2026-07)
- TRIM — Google ドキュメント エディタ ヘルプ (取得日: 2026-07)
- REGEXREPLACE — Google ドキュメント エディタ ヘルプ (取得日: 2026-07)
