スプレッドシートで簡単テキスト変換！SUBSTITUTE関数の使い方と実例解説

2024-11-032026-06-14

Sheet基本

SUBSTITUTE・REPLACE 完全ガイド
文字列の置換・修正を関数で自動化する

手動でCtrl+Hしていた置換作業を関数で自動化する。SUBSTITUTEは「特定の文字列」の置換、REPLACEは「位置指定」での置換。2つの違いと使いどころを解説。

GASとの関係：String.replace() / replaceAll() と対応

GASの str.replace("旧", "新") は SUBSTITUTE の1回置換、str.replaceAll("旧", "新") は全置換と対応します。正規表現が必要なケースはGASの出番です。

GASで自動化を学ぶ →

SUBSTITUTE — 文字列の指定文字を置換

=SUBSTITUTE(文字列, 検索文字, 置換文字, [置換対象の番号])

// "東京都" → "東京" に置換
=SUBSTITUTE(A2, "都", "")

// スペースをすべて除去
=SUBSTITUTE(A2, " ", "")

// 全角スペースも除去する（2回ネスト）
=SUBSTITUTE(SUBSTITUTE(A2, " ", ""), "　", "")

置換対象の番号で「N番目だけ」置換

// "2024-03-15" の最初の"-"だけ置換（2つ目以降はそのまま）
=SUBSTITUTE(A2, "-", "/", 1) // → "2024/03-15"

// 2番目の"-"だけ置換
=SUBSTITUTE(A2, "-", "/", 2) // → "2024-03/15"

REPLACE — 位置と文字数指定で置換

=REPLACE(文字列, 開始位置, 文字数, 新しい文字列)

// "090-1234-5678" の5文字目から4文字を"****"に（マスキング）
=REPLACE(A2, 5, 4, "****") // → "090-****-5678"

// 先頭3文字を削除（空文字列に置換）
=REPLACE(A2, 1, 3, "")

SUBSTITUTEとREPLACEの使い分け

使うべき関数	ケース
SUBSTITUTE	「何という文字列を変えるか」が分かっているとき
REPLACE	「何文字目から何文字分を変えるか」が決まっているとき（コードのマスキング、フォーマット変換など）

実務でよく使うパターン

パターン1：不要な記号・スペースを一括除去

// 電話番号からハイフンを除去
=SUBSTITUTE(A2, "-", "")

// 金額表記から円・コンマを除去して数値化
=VALUE(SUBSTITUTE(SUBSTITUTE(A2, "円", ""), ",", ""))

パターン2：電話番号のマスキング

// "090-1234-5678" → "090-****-5678"
=REPLACE(A2, 5, 4, "****")

パターン3：複数の置換を連続実行

// 株式会社・（株）・(株) をすべて「」に置換
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "株式会社", ""), "（株）", ""), "(株)", "")

パターン4：改行コードを除去（Ctrl+Enterで入った改行）

=SUBSTITUTE(A2, CHAR(10), " ") // 改行をスペースに変換
=SUBSTITUTE(A2, CHAR(10), "") // 改行を削除

この知識をGASで活かす

GASでデータのクリーニングを自動化するとき、SUBSTITUTEで確認した置換パターンをそのまま str.replaceAll() や正規表現に移植できます。まずスプレッドシートで置換ロジックを確認してからGASで自動化する手順が確実です。

GASデータ処理テンプレートを見る → 殿堂入り一覧へ

Free Newsletter

AIを業務に活かしたいなら
SMR-Labメルマガ

毎週火曜10時、コピペで使えるChatGPTプロンプト・
GASテンプレートをお届け。登録は1分・完全無料。

▶ 無料で登録する