🧹 Step 24: TRIM/SUBSTITUTE(文字列整形)

余分な空白を削除し、文字を置き換えてデータをきれいにする！

📋 このステップで学ぶこと

TRIM関数（余分な空白を削除）
SUBSTITUTE関数（文字を置換・削除）
REPLACE関数（位置を指定して置換）
データクレンジングの実例
電話番号・メールアドレスの整形

🎯 1. 文字列整形（データクレンジング）とは

なぜ文字列整形が必要なのか

ユーザーが入力したデータには、余分な空白や不要な記号が含まれることがよくあります。例えば「　　田中太郎　　」のように前後にスペースがあったり、電話番号が「03-1234-5678」と「0312345678」で形式が違ったり。これらを統一してきれいにするのが「データクレンジング」です。

🏠 日常生活に例えると…

データクレンジングは「洗濯」のイメージです。

TRIM = 服についたホコリを払う（余分な空白を削除）
SUBSTITUTE = シミを落とす（不要な文字を別の文字に置き換える）
REPLACE = ボタンを付け替える（特定の位置の文字を変更する）

データクレンジングの重要性

📊 クレンジング前後の比較（※横スクロールできます）

【クレンジング前】問題だらけ

“　　田中太郎　　”     → 前後に余分なスペース
“03-1234-5678”        → ハイフンが入っている
“test＠example.com”   → 全角@になっている

【クレンジング後】きれいなデータ

“田中太郎”            → スペース削除
“0312345678”          → ハイフン削除
“test@example.com”    → 半角@に統一

【なぜ必要？】
・検索やVLOOKUPで一致しない原因になる
・データの集計が正しくできない
・見た目が統一されない

🔑 この章で学ぶ3つの関数

TRIM：余分な空白を削除
SUBSTITUTE：特定の「文字」を置換・削除
REPLACE：特定の「位置」の文字を置換

🧹 2. TRIM関数（余分な空白を削除）

TRIM関数とは

TRIM関数は、文字列の前後の余分な空白を削除し、単語間の空白を1つだけにする関数です。入力ミスで入ってしまった余分なスペースを一発で削除できます。

📝 基本の書き方（※横スクロールできます）

=TRIM(文字列)

【引数の説明】
文字列：空白を削除したいセルまたは文字列

【例】
=TRIM(A1)
→ A1の前後の空白を削除

TRIM関数の3つの効果

📊 どんな空白が削除されるか（※横スクロールできます）

【効果1】前の空白を削除

“　　田中太郎” → “田中太郎”
  ↑ここの空白が消える

【効果2】後ろの空白を削除

“田中太郎　　” → “田中太郎”
          ↑ここの空白が消える

【効果3】単語間の余分な空白を1つに

“田中　　　太郎” → “田中 太郎”
    ↑ここの空白が1つになる

※ 単語間の空白は「削除」ではなく「1つに統一」

具体例

📊 TRIM関数の使用例（※横スクロールできます）

【例1：前後の空白を削除】

     A列                B列           数式
1    元データ            整形後
2    ”  田中太郎  ”     田中太郎      =TRIM(A2)
3    “佐藤花子   ”      佐藤花子      =TRIM(A3)
4    ”   鈴木一郎”      鈴木一郎      =TRIM(A4)

【例2：単語間の余分な空白を1つに】

     A列                   B列              数式
1    元データ               整形後
2    “田中    太郎”        田中 太郎        =TRIM(A2)
3    “東京  都  渋谷”      東京 都 渋谷     =TRIM(A3)

【例3：フォーム入力データのクレンジング】

よくある入力ミス：
・名前の前後にスペースを入力
・コピペで余分な空白が入る
→ TRIM関数で一発修正！

✅ TRIM関数のポイント

前後の空白を完全削除
単語間の空白は1つだけ残す
全角スペースも半角スペースも削除
改行は削除されない（別の方法が必要）

⚠️ 注意点

意図的に入れた複数の空白も1つになってしまう
レイアウト目的の空白には使わないこと
改行を削除したい場合はSUBSTITUTE関数を使う

🔄 3. SUBSTITUTE関数（文字を置換・削除）

SUBSTITUTE関数とは

SUBSTITUTE関数は、文字列の中の特定の文字を別の文字に置き換える関数です。置換先を空白にすれば、削除もできます。

📝 基本の書き方（※横スクロールできます）

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

【引数の説明】
文字列    ：置換元のセルまたは文字列
検索文字列：置き換えたい文字
置換文字列：新しい文字（空白””で削除も可能）
置換対象  ：何番目の一致を置換するか（省略時は全部）

【例】
=SUBSTITUTE(A1, “-“, “”)
→ A1のハイフンをすべて削除

よく使うパターン

📊 SUBSTITUTE関数の使用パターン（※横スクロールできます）

【パターン1】文字を削除する

“03-1234-5678” → “0312345678”

=SUBSTITUTE(A1, “-“, “”)
→ “-“を””（空白）に置換 = 削除

【パターン2】文字を別の文字に置換

“test＠example.com” → “test@example.com”

=SUBSTITUTE(A1, “＠”, “@”)
→ 全角@を半角@に置換

【パターン3】特定の出現だけ置換

“りんご りんご りんご” → “りんご みかん りんご”

=SUBSTITUTE(A1, “りんご”, “みかん”, 2)
→ 2番目の”りんご”だけを”みかん”に置換

具体例

📊 電話番号のハイフン削除（※横スクロールできます）

【例1：ハイフンを削除】

     A列              B列           数式
1    電話番号          ハイフンなし
2    03-1234-5678    0312345678    =SUBSTITUTE(A2,”-“,””)
3    06-9876-5432    0698765432    =SUBSTITUTE(A3,”-“,””)
4    090-1111-2222   09011112222   =SUBSTITUTE(A4,”-“,””)

【解説】
“-“（ハイフン）を””（空の文字列）に置換
= ハイフンを削除

📊 全角記号を半角に（※横スクロールできます）

【例2：全角@を半角@に】

     A列                      B列                  数式
1    メールアドレス            半角変換
2    test＠example.com       test@example.com    =SUBSTITUTE(A2,”＠”,”@”)
3    info＠company.co.jp     info@company.co.jp  =SUBSTITUTE(A3,”＠”,”@”)

【解説】
“＠”（全角@）を”@”（半角@）に置換

複数の置換を組み合わせる（ネスト）

📊 ハイフンとスペースを両方削除（※横スクロールできます）

【例：ネストして複数の文字を削除】

元データ：”03 – 1234 – 5678″
目標　　：”0312345678″

【数式】
=SUBSTITUTE(SUBSTITUTE(A1, “-“, “”), ” “, “”)

【分解して理解】
Step 1: 内側のSUBSTITUTE
  SUBSTITUTE(A1, “-“, “”)
  → “03 – 1234 – 5678” → “03  1234  5678″
  → ハイフンを削除

Step 2: 外側のSUBSTITUTE
  SUBSTITUTE(結果, ” “, “”)
  → “03  1234  5678” → “0312345678”
  → スペースを削除

【結果】
“0312345678”

💡 ネストのコツ

複数の文字を削除したいときは、SUBSTITUTEを入れ子にします。内側から順に処理されます。ただし、3つ以上のネストは読みにくくなるので、別のセルに分けることも検討しましょう。

🔧 4. REPLACE関数（位置を指定して置換）

REPLACE関数とは

REPLACE関数は、文字列の特定の「位置」を指定して置き換える関数です。「3文字目から4文字を****に変える」のように、位置で指定したいときに使います。

📝 基本の書き方（※横スクロールできます）

=REPLACE(文字列, 開始位置, 文字数, 置換文字列)

【引数の説明】
文字列    ：置換元のセルまたは文字列
開始位置  ：何文字目から置換するか
文字数    ：何文字を置換するか
置換文字列：新しい文字列

【例】
=REPLACE(A1, 3, 4, “****”)
→ A1の3文字目から4文字を”****”に置換

SUBSTITUTEとREPLACEの違い

関数	指定方法	使いどころ
SUBSTITUTE	「文字」で指定	ハイフン削除、記号置換など
REPLACE	「位置」で指定	マスク処理、固定位置の変更など

📊 同じ結果でも指定方法が違う（※横スクロールできます）

【元データ】
“A-12345”

【SUBSTITUTE（文字で指定）】
=SUBSTITUTE(A1, “-“, “/”)
→ “-“という文字を”/”に置換
→ 結果: “A/12345”

【REPLACE（位置で指定）】
=REPLACE(A1, 2, 1, “/”)
→ 2文字目から1文字を”/”に置換
→ 結果: “A/12345”

【違い】
SUBSTITUTE: “-“がどこにあっても置換
REPLACE   : 2文字目だけ置換（他の”-“は変わらない）

具体例

📊 電話番号のマスク処理（※横スクロールできます）

【例：電話番号の中間4桁をマスク】

     A列              B列           数式
1    電話番号          マスク後
2    0312345678      03****5678    =REPLACE(A2,3,4,”****”)
3    0698765432      06****5432    =REPLACE(A3,3,4,”****”)

【解説】
=REPLACE(A2, 3, 4, “****”)
→ 3文字目から4文字（1234）を”****”に置換

0312345678
123456789…
  ↑3文字目から4文字（1234）を****に

📊 クレジットカード番号のマスク（※横スクロールできます）

【例：カード番号の中間8桁をマスク】

     A列                   B列                   数式
1    カード番号              マスク後
2    1234567890123456      1234********3456     =REPLACE(A2,5,8,”********”)

【解説】
=REPLACE(A2, 5, 8, “********”)
→ 5文字目から8文字を”********”に置換

🔄 SUBSTITUTE を使う場面

「この文字を置換したい」とき

ハイフンをすべて削除
全角@を半角@に
スペースを削除

🔧 REPLACE を使う場面

「この位置を変更したい」とき

電話番号の中間をマスク
カード番号の一部を隠す
固定長データの一部を変更

💼 5. データクレンジングの実例

実例1：フォーム入力データの整形

📊 名前・電話番号・メールをまとめてクレンジング（※横スクロールできます）

【元データ（問題だらけ）】

     A列                B列              C列
1    名前               電話番号          メール
2    ”  田中太郎  ”    03 – 1234 – 5678  test＠example.com
3    “佐藤   花子”     06-9876-5432      info＠company.co.jp

【クレンジング後】

     D列           E列           F列
1    整形後名前     整形後電話     整形後メール
2    田中太郎      0312345678    test@example.com
3    佐藤 花子     0698765432    info@company.co.jp

【使用する数式】
D2: =TRIM(A2)
    → 前後の空白を削除

E2: =SUBSTITUTE(SUBSTITUTE(B2,”-“,””),” “,””)
    → ハイフンとスペースを削除

F2: =SUBSTITUTE(C2,”＠”,”@”)
    → 全角@を半角@に

実例2：電話番号の形式統一

📊 様々な形式を統一（※横スクロールできます）

【様々な入力形式】

     A列                  B列           数式
1    入力された電話番号    統一形式
2    03-1234-5678        0312345678    =SUBSTITUTE(SUBSTITUTE(A2,”-“,””),” “,””)
3    03 1234 5678        0312345678    =SUBSTITUTE(SUBSTITUTE(A3,”-“,””),” “,””)
4    (03)1234-5678       0312345678    ※下記参照
5    ０３１２３４５６７８   0312345678    ※下記参照

【A4の数式（カッコも削除）】
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,”-“,””),” “,””),”(“,””),”)”,””)

【A5の数式（全角数字を半角に）】
=ASC(A5)
※ASC関数で全角英数字を半角に変換

実例3：郵便番号の形式統一

📊 ハイフンあり/なしを統一（※横スクロールできます）

【ハイフンを削除して統一】

     A列           B列         数式
1    郵便番号       統一形式
2    150-0001     1500001     =SUBSTITUTE(A2,”-“,””)
3    1500001      1500001     =SUBSTITUTE(A3,”-“,””)

【ハイフンを追加して統一】

     A列           B列           数式
1    郵便番号       統一形式
2    1500001      150-0001     =LEFT(A2,3)&”-“&RIGHT(A2,4)
3    150-0001     150-0001     =IF(FIND(“-“,A3),A3,LEFT(A3,3)&”-“&RIGHT(A3,4))

💡 6. 応用テクニック

テクニック1：改行を削除する

📊 CHAR関数との組み合わせ（※横スクロールできます）

【改行の削除】

TRIM関数では改行は削除されない
→ SUBSTITUTE関数とCHAR関数を使う

=SUBSTITUTE(A1, CHAR(10), “”)
→ Windowsの改行（LF）を削除

=SUBSTITUTE(A1, CHAR(13), “”)
→ Macの改行（CR）を削除

=SUBSTITUTE(SUBSTITUTE(A1, CHAR(10), “”), CHAR(13), “”)
→ 両方の改行を削除

テクニック2：大文字・小文字の変換

📊 UPPER/LOWER/PROPER関数（※横スクロールできます）

【大文字・小文字の変換】

=UPPER(A1)   → すべて大文字に
              “abc” → “ABC”

=LOWER(A1)   → すべて小文字に
              “ABC” → “abc”

=PROPER(A1)  → 先頭だけ大文字に
              “john smith” → “John Smith”

【活用例】
メールアドレスを小文字に統一:
=LOWER(TRIM(A1))

テクニック3：複数の関数を組み合わせる

📊 完全なクレンジング数式（※横スクロールできます）

【名前のクレンジング】
=TRIM(A1)

【電話番号のクレンジング】
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,”-“,””),” “,””),”　”,””)
※ 全角スペースも削除

【メールアドレスのクレンジング】
=LOWER(TRIM(SUBSTITUTE(A1,”＠”,”@”)))
※ 空白削除 + 全角@変換 + 小文字化

⚠️ 7. よくある間違いと注意点

間違い1：SUBSTITUTEの引数の順番

❌ よくある間違い（※横スクロールできます）

【間違い】
=SUBSTITUTE(“”, “a”, “abc”)
→ 引数の順番が違う！

【正しい】
=SUBSTITUTE(“abc”, “a”, “”)
→ (元の文字列, 検索文字, 置換文字) の順番

間違い2：TRIMで改行は削除されない

❌ よくある間違い（※横スクロールできます）

【間違い】
セル内改行がある場合にTRIMを使う
→ 改行は削除されない！

【正しい】
=SUBSTITUTE(TRIM(A1), CHAR(10), “”)
→ TRIMで空白削除 + SUBSTITUTEで改行削除

間違い3：全角スペースが残る

❌ よくある間違い（※横スクロールできます）

【注意】
TRIMは全角スペースも削除するが、
特殊な空白文字（ノーブレークスペース等）は残ることがある

【確実に削除する方法】
=SUBSTITUTE(TRIM(A1), “　”, “”)
→ TRIMの後に全角スペースも明示的に削除

⚠️ 元データを残す重要性

データクレンジングを行う際は、元のデータは別の列に残しておくことをおすすめします。間違えてクレンジングしてしまった場合に、元に戻せるようにするためです。

💡 クレンジングの手順

元データをコピーして別シートにバックアップ
新しい列にクレンジング数式を作成
結果を確認
問題なければ「値のみ貼り付け」で確定

📝 練習問題

練習 1

初級

TRIM関数で前後の空白を削除してください

     A列                B列
  元データ            整形後
  ”  田中太郎  ”     ?
  “佐藤花子   ”      ?

解答:

B2に =TRIM(A2)

B2をB3にコピー

結果:

B2: 田中太郎
B3: 佐藤花子

練習 2

初級

SUBSTITUTE関数で電話番号のハイフンを削除してください

     A列              B列
  電話番号          ハイフンなし
  03-1234-5678    ?
  06-9876-5432    ?

解答:

B2に =SUBSTITUTE(A2,"-","")

B2をB3にコピー

結果:

B2: 0312345678
B3: 0698765432

練習 3

中級

SUBSTITUTEを2回使って、ハイフンとスペースの両方を削除してください

     A列                  B列
  電話番号              整形後
  03 – 1234 – 5678    ?
  06 – 9876 – 5432    ?

解答:

B2に =SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","")

B2をB3にコピー

結果:

B2: 0312345678
B3: 0698765432

解説: 内側でハイフンを削除、外側でスペースを削除

練習 4

中級

全角@を半角@に置換してください

     A列                      B列
  メールアドレス            半角変換
  test＠example.com       ?
  info＠company.co.jp     ?

解答:

B2に =SUBSTITUTE(A2,"＠","@")

B2をB3にコピー

結果:

B2: test@example.com
B3: info@company.co.jp

練習 5

上級

REPLACE関数で電話番号の中間4桁をマスク（****）にしてください

     A列              B列
  電話番号          マスク後
  0312345678      03****5678
  0698765432      06****5432

解答:

B2に =REPLACE(A2,3,4,"****")

B2をB3にコピー

結果:

B2: 03****5678
B3: 06****5432

解説: 3文字目から4文字を「****」に置換

練習 6

上級

TRIM、SUBSTITUTEを組み合わせて、名前と電話番号を完全にクレンジングしてください

     A列              B列              C列          D列
  名前            電話番号          整形後名前    整形後電話
  ”  田中太郎  ”  03 – 1234 – 5678 ?           ?
  “佐藤   花子”   06-9876-5432     ?           ?

解答:

C2に =TRIM(A2)

D2に =SUBSTITUTE(SUBSTITUTE(B2,"-","")," ","")

C2とD2をC3、D3にコピー

結果:

C2: 田中太郎、D2: 0312345678
C3: 佐藤花子、D3: 0698765432

📝 Step 24 のまとめ

✅ このステップで学んだこと

TRIM関数：前後の余分な空白を削除、単語間の空白を1つに
SUBSTITUTE関数：特定の「文字」を別の文字に置換・削除
REPLACE関数：特定の「位置」の文字を置換
使い分け：SUBSTITUTE=文字で指定、REPLACE=位置で指定
ネスト：複数のSUBSTITUTEを組み合わせて複数の文字を処理
データクレンジング：フォーム入力データの整形に必須のスキル

🎯 次のステップの予告

文字列整形をマスターしました！次のStep 25では、「統計関数の基礎」を学びます。MEDIAN（中央値）、MODE（最頻値）、STDEV（標準偏差）など、データ分析に必要な統計関数を習得します。

❓ よくある質問

Q1: TRIMで改行は削除されますか？

いいえ、TRIMは空白（スペース）だけを削除します。改行を削除したい場合は、SUBSTITUTE関数を使います。=SUBSTITUTE(A1,CHAR(10),"")でWindowsの改行を削除できます。

Q2: SUBSTITUTEとREPLACEの違いは何ですか？

SUBSTITUTE：「何の文字」を置換するか指定（文字で指定）
REPLACE：「何文字目」を置換するか指定（位置で指定）

ハイフンを削除したいときはSUBSTITUTE、3文字目から5文字をマスクしたいときはREPLACEを使います。

Q3: 複数の文字を一度に置換できますか？

SUBSTITUTEをネストすることで可能です。例：=SUBSTITUTE(SUBSTITUTE(A1,"-","")," ","")でハイフンとスペースを両方削除できます。3つ以上でもネスト可能ですが、読みにくくなるので5個以上は別セルに分けることも検討してください。

Q4: 全角文字と半角文字を一括変換できますか？

はい、ASC関数とJIS関数があります。

=ASC(A1)：全角英数字を半角に変換
=JIS(A1)：半角カナを全角に変換

Q5: 元のデータを上書きしても大丈夫ですか？

いいえ、元データは残しておくことを推奨します。別の列に数式を作成し、結果を確認してから「コピー＆値貼り付け」で元の列に戻すのが安全です。

Q6: 大文字・小文字を変換できますか？

はい、専用の関数があります。

=UPPER(A1)：すべて大文字に
=LOWER(A1)：すべて小文字に
=PROPER(A1)：先頭だけ大文字に

Q7: SUBSTITUTEは大文字・小文字を区別しますか？

はい、区別します。「a」と「A」は別の文字として扱われます。大文字・小文字を区別せずに置換したい場合は、まずUPPER関数やLOWER関数で統一してからSUBSTITUTEを使うとよいでしょう。

📝

学習メモ

Excel・Googleスプレッドシート完全マスター - Step 24

📋 過去のメモ一覧 ▼