関数辞典

SUBSTITUTE関数の使い方・読み方｜文字列の一部を置き換える

執筆者:

関数辞典編集部

カテゴリ:

SUBSTITUTE関数の使い方・読み方のイメージ

文字列

SUBSTITUTE関数の使い方・読み方

Excel関数辞典

読み方：SUBSTITUTE は「サブスティテュート」と読みます。（一般的な読み方です。公式に読み仮名の定めはありません）

SUBSTITUTEは、文字列の中の特定の文字を、別の文字に置き換える関数です。

使える環境Excel○Googleスプレッドシート○
このページの中身（24項目）
  1. 書式
  2. 基本の使い方
  3. 特定の1つだけを置き換える
  4. REPLACEとの違い
  5. データ整形の定番パターン
  6. 出現回数を数える裏技
  7. 最後の区切りより後ろを取り出す
  8. 全行に適用する
  9. 注意点
  10. Excelとの違い
  11. 置換の関数が二つある理由
  12. 整形の定番として使う
  13. 何番目だけを置き換える
  14. 置換と正規表現の使い分け
  15. 整形用の作業列を標準化する
  16. 表記ゆれを吸収する
  17. 「文字列」の関数を全体から見る
  18. 数式を書く前の手順
  19. どの関数でも共通する確認
  20. 引き継ぐときに残すこと
  21. このサイトの収録状況
  22. よくある質問
  23. まとめ
  24. 関連する関数

この記事の書式と対応環境の出どころ（2026-08-26 確認）

関数の仕様は追加・変更されることがあります。書式が合わないときは、上の公式ページで最新をご確認ください。

「ハイフンを削除する」「全角スペースを半角に統一する」といったデータ整形で頻繁に使います。ExcelとGoogleスプレッドシートで共通です。

SUBSTITUTE関数の使い方の記事構成図
SUBSTITUTE関数の使い方：この記事で扱う内容

書式

=SUBSTITUTE(文字列, 検索文字, 置換文字, [何番目])
引数内容
文字列対象
検索文字置き換えたい文字
置換文字置き換え後の文字
何番目省略するとすべて置き換える

基本の使い方

A1に 03-1234-5678 が入っているとします。

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

置換文字を ""（空文字）にすると、削除になります。これが最もよく使う形です。

SUBSTITUTE関数の使い方の要点図
SUBSTITUTE関数の使い方

特定の1つだけを置き換える

第4引数で指定します。

=SUBSTITUTE(A1, "-", "／", 2)   → 03-1234／5678

2つ目の「-」だけが変わります。すべてではなく1箇所だけ変えたいときに使います。

REPLACEとの違い

似た関数に REPLACE があります。

SUBSTITUTEREPLACE
指定方法文字で指定位置で指定
使う場面特定の文字を置換何文字目から何文字を置換
=SUBSTITUTE(A1, "-", "")     文字「-」を消す
=REPLACE(A1, 3, 1, "")       3文字目の1文字を消す

位置が固定なら REPLACE、文字で判断するなら SUBSTITUTE です。実務ではSUBSTITUTEのほうが圧倒的に使用頻度が高くなります。

データ整形の定番パターン

不要な文字をまとめて削除する

SUBSTITUTEを入れ子にします。

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

ハイフン、半角スペース、全角スペースを全部削除しています。入れ子は内側から実行されるので、順番を意識してください。

数が多い場合は REGEXREPLACE のほうが短く書けます。

=REGEXREPLACE(A1, "[- 　]", "")

全角を半角に統一する

数字や英字なら ASC が使えます。

=ASC(A1)

特定の文字だけならSUBSTITUTEです。

=SUBSTITUTE(A1, "　", " ")

改行を削除する

=SUBSTITUTE(A1, CHAR(10), "")

CSVやWebからコピーしたデータには、見えない改行が入っていることがあります。LEN() で文字数を確認して、見た目と合わなければこれを疑ってください。

単位を外して数値にする

=VALUE(SUBSTITUTE(A1, "円", ""))

1,234円 のようにカンマも入っているなら、両方外します。

=VALUE(SUBSTITUTE(SUBSTITUTE(A1, "円", ""), ",", ""))

出現回数を数える裏技

SUBSTITUTEの応用でよく使われる書き方です。

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

「元の長さ」から「その文字を全部消した長さ」を引くと、その文字の個数になります。

複数文字の場合は、文字数で割ります。

=(LEN(A1) - LEN(SUBSTITUTE(A1, "東京", ""))) / LEN("東京")

「東京」が何回出てくるかを数えられます。

最後の区切りより後ろを取り出す

SUBSTITUTEの第4引数を使った定番テクニックです。

=RIGHT(A1, LEN(A1) - FIND("|", SUBSTITUTE(A1, "/", "|", LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

「最後の / だけを | に置き換えて、その位置を探す」という仕組みです。

読みにくいので、実務では REGEXEXTRACT を推奨します。

=REGEXEXTRACT(A1, "([^/]+)$")

全行に適用する

=ARRAYFORMULA(IF(A2:A="", "", SUBSTITUTE(A2:A, "-", "")))

注意点

大文字小文字を区別します。

=SUBSTITUTE("ABC abc", "a", "X")   → ABC Xbc

区別せずに置換したいなら REGEXREPLACE を使ってください。

=REGEXREPLACE(A1, "(?i)a", "X")

結果は文字列になります

数値として使うなら VALUE で戻してください。

Excelとの違い

SUBSTITUTE・REPLACE ともに共通です。書式も挙動も同じです。

違いは正規表現系の関数です。

Excelスプレッドシート
REGEXREPLACE無い（365の一部を除く）ある
REGEXEXTRACT無いある

Excelでは複雑な置換にSUBSTITUTEの入れ子を使うしかありませんが、スプレッドシートなら正規表現が使えます。置換対象が3つを超えたら、正規表現に切り替えるのが実用的です。

置換の関数が二つある理由

文字を置き換える関数には二種類あります。文字列を指定して置き換えるものと、位置と文字数を指定して置き換えるものです。名前が似ているため混同されがちですが、用途はまったく違います。

文字列を指定するタイプは、「この文字を、この文字に」という指定をします。対象がどこにあるかは関係なく、見つけたものをすべて置き換えます。ハイフンを削除する、全角の空白を半角に揃える、といった整形の用途はこちらです。

位置を指定するタイプは、「左から何文字目から、何文字分を、この文字に」という指定をします。中身が何であるかは関係なく、位置だけで決めます。電話番号の一部を伏せ字にする、固定長のコードの一部を書き換える、といった用途に使います。

日常的に使うのは前者です。データの整形はほぼ前者で足ります。後者が必要になるのは、桁位置が決まっている形式を扱う場合に限られます。

整形の定番として使う

取り込んだデータの整形では、置換が中心的な役割を果たします。よく使う処理を挙げておきます。

ハイフンや空白の除去です。電話番号や郵便番号が「03-1234-5678」の形で入っている場合、ハイフンを空文字に置き換えれば数字だけになります。突き合わせのキーとして使うなら、形式を揃えておく必要があります。

全角と半角の統一です。数字やアルファベットが全角で入っていると、半角の値とは一致しません。置換で揃えるか、専用の変換関数を使います。

改行の除去です。フォームの回答やメールから貼り付けたデータには、セルの中に改行が入っていることがあります。改行を表す関数を指定して置き換えれば取り除けます。

見えない空白の除去です。前後の空白を落とす関数がありますが、これは半角の空白しか対象にしません。全角の空白は置換で先に半角にしてから、落とす関数を通す必要があります。

これらを組み合わせた整形用の作業列を作っておくと、取り込みのたびに同じ処理が自動で行われます。数式の中で毎回処理するより、はるかに保守しやすくなります。

何番目だけを置き換える

文字列を指定するタイプの関数には、何番目に出てきたものを置き換えるかを指定する引数があります。省略するとすべてが置き換わりますが、指定すればその回だけになります。

これが役に立つのは、区切り文字の一部だけを変えたい場合です。「東京都-新宿区-西新宿」という文字列で、最初のハイフンだけを別の記号に変えたい、といった処理ができます。

応用として、最後の区切り文字の位置を特定する技法があります。まず区切り文字が何個あるかを数え、その番号を指定して特殊な文字に置き換えます。その特殊な文字の位置を検索すれば、最後の区切りの位置が分かります。ファイル名から拡張子を取り出す、階層のあるコードから末尾の要素を取り出す、といった処理に使えます。

やや技巧的ですが、覚えておくと「最後の区切り以降を取り出す」という頻出の要求に答えられます。

置換と正規表現の使い分け

置換の関数は、指定した文字列と完全に一致する部分だけを対象にします。柔軟な条件で置き換えたい場合は、正規表現を使う関数があります。

数字だけをすべて削除する、特定の形式に一致する部分だけを置き換える、複数のパターンをまとめて処理する。こうした処理は正規表現のほうが短く書けます。

一方で、正規表現は書き方を覚える必要があり、意図しない部分に一致する危険もあります。単純な置換で足りる場面では、素直に置換の関数を使うほうが安全です。

判断の目安としては、置換の対象が固定の文字列なら置換の関数、パターンで指定する必要があるなら正規表現、と考えてください。→ REGEXREPLACE関数の使い方

なお、正規表現の関数はスプレッドシート特有のものが多く、他の環境では使えないことがあります。ファイルを共有する予定があるなら確認してください。

整形用の作業列を標準化する

取り込んだデータを扱う場面では、整形の処理をひとまとめにしておくと運用が安定します。

推奨する構成は、取り込んだ列の右隣に整形済みの列を作る形です。列名は元の名前に「整形済」などを付けて区別します。

整形の内容は、次の順で行います。まず全角の空白を半角に置き換え、次に前後の空白を落とし、次に改行を除去し、最後に必要なら全角英数を半角に揃えます。この順序であれば、取りこぼしが起きません。

一つのセルに複数の置換を重ねると数式が長くなりますが、処理の順序が明確になる利点もあります。読みにくいと感じるなら、段階ごとに列を分けてください。

整形済みの列を作ったら、以降の処理はすべてその列を参照します。検索も集計も突き合わせも、整形済みの値で行えば一致の問題が起きません。

元の列は残しておいてください。整形の処理に誤りが見つかったときに、やり直せます。

この構成を一度作れば、取り込みのたびに自動で整形されます。手作業の工程がなくなり、処理の漏れも起きません。

表記ゆれを吸収する

同じ内容が違う表記で入力されていると、集計が分断されます。置換はこれを吸収する手段になります。

株式会社の表記ゆれは典型例です。前株と後株、正式表記と略記、括弧の全角半角。これらを揃えないと、同じ会社が別々に集計されます。

住所も同様です。丁目や番地の表記、ハイフンの有無、算用数字と漢数字。突き合わせのキーにする場合は、揃える必要があります。

対処としては、置換で正規化した列を作ります。株式会社を空文字に置き換えて社名だけにする、ハイフンを除去して数字だけにする。突き合わせにはこの正規化した列を使い、表示には元の列を使います。

ただし、正規化しすぎると別の会社が同一視される危険もあります。どこまで揃えるかは、データの性質を見て判断してください。

根本的には、入力の時点で揃えるのが最善です。プルダウンやマスタからの選択にすれば、ゆれは生まれません。既存のデータを整えることと、今後のゆれを防ぐこと。両方を進めてください。

「文字列」の関数を全体から見る

文字列は文字を加工する関数群です。同じカテゴリの関数は、つまずく場所も似ています。この記事のほかに31本あります。

記事
CHAR
CLEAN
CODE
CONCAT
CONCATENATE
EXACT
FIND
FIXED
HYPERLINK
IMAGE
JIS
LEFT
LEN
LENB
LOWER
MID
NUMBERVALUE
PROPER
REGEXEXTRACT
REGEXMATCH
REGEXREPLACE
REPLACE
REPT
RIGHT
SEARCH
SPLIT
TEXT
TEXTJOIN
TRIM
UPPER
VALUE

このカテゴリで共通するつまずき

全角と半角、前後の空白、改行。この3つが原因のほとんどです。1つで理解した内容は、同じカテゴリの他の関数にもそのまま使えます。

どれを使うかの決め方

位置で取るのか、記号を目印に取るのかで使う関数が変わります。関数の一覧を眺めて選ぼうとすると、どれも当てはまるように見えて決まりません。

迷ったときは

→ 全関数の一覧 に目的別の索引があります。

数式を書く前の手順

書き始める前に決めておくことがあります。ここが曖昧なまま書くと、途中で行き詰まります。

1. 出したい結果を一文で書く

「何を、どの条件で、どんな形で出したいか」。この一文が書ければ、関数はほぼ決まります。書けないなら、まだ要件が固まっていません。

2. 元データの型を確認する

全角と半角、前後の空白、改行。この3つが原因のほとんどです。=ISNUMBER(A2) で数値かどうかを確認できます。見た目が同じでも、数値と文字列は別物です。

3. 空欄と0の扱いを決める

未入力なのか、実績が0なのか。表の意味が変わります。どちらとして扱うかを決めてから書いてください。

4. 条件の数を数える

位置で取るのか、記号を目印に取るのかで使う関数が変わります。将来条件が増える可能性があるなら、最初から複数条件に対応する形にしてください。

5. 共有先の環境を確認する

新しい関数は、古い環境で開くとエラーになります。社外に渡すファイルでは、この一点で選択肢が変わります。

6. 1行だけ書いて確認する

全行にコピーする前に、1行分の結果が正しいかを見てください。間違ったまま広げると、直す手間が増えます。

どの関数でも共通する確認

関数を問わず、結果がずれる原因は限られています。この5つを確認すれば、大半が解決します。

参照を固定したか

下方向にコピーする数式では、動いてはいけない参照を固定します。上の数行だけ見て判断しないでください。ずれは下のほうで表面化します。

範囲の行数が揃っているか

複数の範囲を渡す関数では、行数がずれると結果が狂います。エラーにならず値が返るため、気づきにくい種類の間違いです。

型が揃っているか

=ISNUMBER(A2)

数値として入っているかを判定します。システムから書き出したデータでは、これが原因のことが多くあります。

前後に空白が入っていないか

=LEN(A2)&" / "&LEN(TRIM(A2))

数が違えば、余分な空白が入っています。見た目では判別できません。

別の方法で検算したか

同じ数字を別の関数でも出して、突き合わせてください。1つの結果だけを見ても、正しいかどうかは判断できません。

最終行まで確認したか

一番下までスクロールして、結果を見てください。途中から値が変わっていないかを確認します。

引き継ぐときに残すこと

数式は動いていても、意図は残りません。渡す前に、次の点をシート内にメモしてください。3分で終わります。

何を出している数式か

一行で構いません。読めば分かると思っても、数か月後の自分には分かりません。

どの表を参照しているか

参照先のシート名と、更新の担当。別ファイルなら、その場所も書いてください。

該当しないときの扱い

空欄にしているのか、文言を出しているのか、エラーのまま残しているのか。書いていないと、受け取った側は空欄をデータなしと読みます。

触ってはいけない場所

列を挿入すると壊れる数式がある場合、必ず書き残してください。この一行があるだけで、引き継ぎ後の事故が大きく減ります。

使った関数の対応環境

新しい関数を使っている場合、古い環境では開けません。共有の範囲が広がる可能性があるなら、明記してください。

確定した期間は値にする

締めた月の集計は、値貼り付けにしてください。参照元を消しても壊れなくなり、ファイルも軽くなります。

このサイトの収録状況

現在、201本の記事をカテゴリ別に整理しています。関数名が分からない場合は、カテゴリから探してください。

カテゴリ記事数
目的別51本
集計47本
文字列31本 ←この記事
日付19本
検索・参照15本
条件15本
機能12本
配列・抽出10本
関数一覧1本

探し方の順番

関数名が分かっているなら検索窓から。やりたいことだけ決まっているなら「目的別」から。近い関数を比べたいなら、同じカテゴリの一覧から入ってください。

記事の構成

どの記事も、書式・実例・つまずきやすいところ・似た関数との使い分けを同じ順番で並べています。1本読めば、他の記事も同じ場所を探せます。

よくある質問

Q. 置き換えたのに一致しません

対象の文字列に、見えない文字が含まれている可能性があります。全角と半角の違いも確認してください。

Q. 複数の文字をまとめて置き換えたいのですが

関数を入れ子にして重ねる方法があります。数が多い場合は、正規表現を使うか、対応表を作って順に処理するほうが管理しやすくなります。

Q. 大文字と小文字は区別されますか

区別されます。区別せずに置き換えたい場合は、先に大文字か小文字に揃えてください。

Q. 何も置き換わりません

指定した文字列が対象の中に存在しません。文字数を数えて、想定どおりの中身かを確かめてください。

Q. 削除したいのですが

置き換え後の文字列に空文字を指定すれば、削除になります。

Q. 元のセルを直接書き換えられますか

関数は結果を別のセルに返します。元のセルを書き換えたい場合は、結果を値として貼り付けてください。

Q. 改行を削除したいのですが

改行を表す関数を対象として指定してください。環境によって改行の表現が異なる場合があります。

Q. 一括で置換する機能とどちらがよいですか

一度きりの処理なら機能のほうが早く済みます。取り込みのたびに繰り返す処理なら、関数にしておくほうが確実です。

まとめ

  • 書式は =SUBSTITUTE(文字列, 検索文字, 置換文字)
  • 置換文字を "" にすると削除になる
  • 第4引数で何番目だけを置き換えられる
  • 位置で指定するなら REPLACE、文字で指定するならSUBSTITUTE
  • LEN(A1)-LEN(SUBSTITUTE(...)) で出現回数が数えられる
  • 大文字小文字を区別する。区別しないなら REGEXREPLACE

関連する関数