関数辞典

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

執筆者:

admin

カテゴリ:

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つを超えたら、正規表現に切り替えるのが実用的です。

まとめ

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

関連する関数

コメント

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

投稿をさらに読み込む