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

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

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

書式

=SUBSTITUTE(文字列, 検索文字, 置換文字, [何番目])

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

基本の使い方

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

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

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

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

第4引数で指定します。

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

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

REPLACEとの違い

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

	SUBSTITUTE	REPLACE
指定方法	文字で指定	位置で指定
使う場面	特定の文字を置換	何文字目から何文字を置換

=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関数の使い方

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

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

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

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

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

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

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

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

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

表記ゆれを吸収する

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

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

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

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

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

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

よくある質問

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

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

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

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

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

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

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

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

Q. 削除したいのですが

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

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

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

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

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

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

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

まとめ

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