MENU
Excel

ExcelのTRIM・SUBSTITUTE関数｜スペース削除と文字列置換の使い方

しんたろ。

Excelでデータを扱っていると、「余分なスペースのせいでVLOOKUPが一致しない」「コピペしたデータに不要な文字が混入している」といった問題に直面することがあります。

そんなときに活躍するのがTRIM関数とSUBSTITUTE関数です。TRIM関数は余分なスペースを一括削除し、SUBSTITUTE関数は指定した文字列を自由に置換・削除できます。

本記事では、この2つの関数の基本から応用まで、実務で即使えるデータクリーニングテクニックを徹底解説します。

TRIM関数とSUBSTITUTE関数は「データ整形の二大関数」です。この2つを組み合わせるだけで、VLOOKUPの不一致問題やCSVデータの文字化けなど、よくあるトラブルの大半を解決できますよ。

この記事でわかること

  • TRIM関数で余分なスペースを一括削除する方法
  • SUBSTITUTE関数で文字列を自由に置換・削除する方法
  • 全角スペースや改行コードを除去するテクニック
  • VLOOKUP前のデータクリーニング手順
  • SUBSTITUTE関数で特定文字の出現回数を数える方法
  • VBAで文字列置換を自動化するマクロ
💻 TRIM・SUBSTITUTE関数を体験してみよう！

A列の「問題のあるデータ」をクリックすると、TRIM・SUBSTITUTE関数でクリーニングされた結果が表示されます。

X
データクリーニング.xlsx
← A列のデータをクリックして関数の効果を確認 =TRIM(A2) → “東京都新宿区”（前後スペース除去） =TRIM(SUBSTITUTE(A3,”　”,” “)) → “田中 太郎”（全角→半角+整形） =SUBSTITUTE(A4,”-“,””) → “09012345678”（ハイフン削除） =SUBSTITUTE(A5,”株式会社”,”(株)”) → “(株)山田商事” =TRIM(CLEAN(SUBSTITUTE(A6,”　”,””))) → “データ整形完了”（万能数式）
A（問題データ）B（使用関数）C（結果）
1元データ数式クリーニング結果
2 –TRIM(A2) –東京都新宿区
3 –TRIM(SUBSTITUTE(…)) –田中 太郎
4 –SUBSTITUTE(A4,”-“,””) –09012345678
5 –SUBSTITUTE(…,”(株)”) –(株)山田商事
6 –TRIM(CLEAN(SUB…)) –データ整形完了
✅ TRIM関数 — 先頭と末尾の半角スペースを除去。VLOOKUPが一致しない原因No.1がこの「見えないスペース」です。
✅ TRIM + SUBSTITUTE — 全角スペース（TRIM非対応）をSUBSTITUTEで半角に変換し、TRIMで連続スペースを1つにまとめます。
✅ SUBSTITUTE（削除） — 置換文字列に””（空文字）を指定すると文字の削除になります。ハイフン除去は電話番号の正規化でよく使います。
✅ SUBSTITUTE（置換） — 「株式会社」を「(株)」に置き換え。大文字・小文字は区別される点に注意。
💡 万能クリーニング数式 — TRIM(CLEAN(SUBSTITUTE(A1,”　”,””)))で、制御文字+全角スペース+前後スペースを一括処理。外部データ取込み時の定番です。
▶ A列の「問題のあるデータ」をクリックして、関数によるクリーニング結果を確認しよう△マークはスペースを表しています。実際のExcelでは目に見えないため、TRIMが重宝します。
この記事の目次
  1. TRIM関数・SUBSTITUTE関数とは？データ整形の必須ツール
  2. TRIM関数｜スペース削除の基本と応用
  3. SUBSTITUTE関数｜文字列置換の基本と応用
  4. LINEでExcelを気軽に学べる
  5. 実践編｜データクリーニングのテクニック集
  6. TRIM・SUBSTITUTEと他の関数の組み合わせ
  7. VBAで文字列置換を自動化
  8. よくある質問（FAQ）
  9. まとめ
  10. 関連記事

TRIM関数・SUBSTITUTE関数とは？データ整形の必須ツール

Excelで外部データを取り込んだり、他のシステムからコピペしたりすると、不要なスペースや余計な文字が紛れ込むことがよくあります。こうした「目に見えないゴミ」がVLOOKUPの不一致やSUMIFSのカウントミスの原因になります。

TRIM関数は余分なスペースを削除する関数、SUBSTITUTE関数は指定した文字列を別の文字列に置き換える関数です。この2つを使いこなせば、データクリーニングの大半をカバーできます。

TRIM関数の構文 — 余分なスペースを一括削除

=TRIM(文字列)

引数: 文字列（またはセル参照）を1つだけ指定します。

動作: 先頭と末尾のスペースを削除し、単語間の連続スペースを1つにまとめます。

注意: TRIM関数が削除するのは半角スペース（ASCII 32）のみです。全角スペースは削除されません。

SUBSTITUTE関数の構文 — 文字列を自由に置換

=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])
引数必須/任意説明
文字列必須対象のテキストまたはセル参照
検索文字列必須置き換えたい文字列
置換文字列必須置き換え後の文字列（””で削除）
置換対象任意N番目の出現のみ置換（省略時は全置換）

REPLACE関数との違い

SUBSTITUTE関数と混同しやすいのがREPLACE関数です。両者の違いを明確にしておきましょう。

項目SUBSTITUTEREPLACE
指定方法文字列で検索して置換位置（何文字目）を指定して置換
構文=SUBSTITUTE(A1,”旧”,”新”)=REPLACE(A1,開始位置,文字数,”新”)
用途特定の文字を全て置換したい固定位置の文字を差し替えたい
例ハイフンを全て削除3文字目から2文字を差し替え

使い分けのポイント: 「何を置き換えるか」がわかっているならSUBSTITUTE、「どの位置を置き換えるか」がわかっているならREPLACEを使います。実務ではSUBSTITUTEの方が圧倒的に使用頻度が高いです。

TRIM関数｜スペース削除の基本と応用

先頭・末尾のスペース削除

最もよく使うパターンです。データベースやWebフォームから取り込んだデータの先頭や末尾に余分なスペースが入っていることがあります。

' セルA1に「 東京都新宿区 」（前後にスペース）が入っている場合
=TRIM(A1)
→ 結果: 「東京都新宿区」

見た目では気づきにくいスペースも、TRIM関数で一発で除去できます。特にVLOOKUPで「データがあるのに一致しない」というトラブルの原因は、このスペース混入が非常に多いです。

単語間の余分なスペースを1つにまとめる

TRIM関数は先頭・末尾だけでなく、単語間の連続スペースも1つにまとめてくれます。

' セルA1に「田中　　　太郎」（スペース3つ）が入っている場合
=TRIM(A1)
→ 結果: 「田中 太郎」（スペース1つに統一）

名前リストの整形や、英文テキストの余分なスペース除去に便利です。

全角スペースを削除する方法（TRIM非対応）

TRIM関数は半角スペースのみを処理します。日本語データでよく混入する全角スペースは削除できません。全角スペースも含めて削除するには、SUBSTITUTE関数と組み合わせます。

' 半角スペース + 全角スペースの両方を削除
=TRIM(SUBSTITUTE(A1,"　",""))

' 全角スペースを半角スペースに変換してからTRIMで整形
=TRIM(SUBSTITUTE(A1,"　"," "))

上の式は全角スペースを完全削除、下の式は全角スペースを半角スペースに変換してからTRIMで整形します。名前の姓と名の間にスペースを残したい場合は下の式がおすすめです。

TRIM × CLEAN で制御文字も除去

外部システムからコピーしたデータには、印刷できない制御文字（改行、タブなど）が含まれていることがあります。CLEAN関数と組み合わせることで、制御文字とスペースを同時に除去できます。

' 制御文字 + 余分なスペースを一括除去
=TRIM(CLEAN(A1))

' さらに全角スペースも除去する完全版
=TRIM(CLEAN(SUBSTITUTE(A1,"　","")))

この=TRIM(CLEAN(SUBSTITUTE(A1,”　”,””)))は「データクリーニングの万能数式」として覚えておくと便利です。外部データを取り込んだ際の最初の処理としてルーチン化しましょう。

筆者の実務Tips

Excel歴10年で断言できますが、TRIM関数を最も使うタイミングは「他システムからのCSVインポート直後」です。基幹システムやWebフォームから出力されたデータには、目に見えない半角スペースが紛れていることが非常に多く、これがVLOOKUPの不一致を引き起こす原因の約7割を占めます。筆者はデータ取込時に必ず「=TRIM(CLEAN(A1))」を全列に適用する作業列を作るルーチンを組んでおり、これだけでトラブル対応の工数が激減しました。

この操作でよくある疑問

Q: TRIM関数を使っても空白が消えない場合はどうすればいいですか？

A: TRIM関数は半角スペース（文字コード32）のみ対応しています。全角スペースやノーブレークスペース（CHAR(160)）は削除されません。SUBSTITUTE(A1,”　”,””)で全角スペースを、SUBSTITUTE(A1,CHAR(160),””)でノーブレークスペースを個別に除去してからTRIMを適用してください。

SUBSTITUTE関数｜文字列置換の基本と応用

基本的な文字列の置換

SUBSTITUTE関数の最も基本的な使い方は、特定の文字列を別の文字列に置き換えることです。

' 「株式会社」を「(株)」に置換
=SUBSTITUTE(A1,"株式会社","(株)")
→ 「株式会社山田商事」→「(株)山田商事」

' 「東京都」を「東京」に置換
=SUBSTITUTE(A1,"東京都","東京")
→ 「東京都渋谷区」→「東京渋谷区」

SUBSTITUTE関数は大文字と小文字を区別します。「ABC」と「abc」は異なる文字列として扱われる点に注意してください。

特定の文字を削除する（空文字に置換）

置換文字列に空文字（””）を指定すると、特定の文字を削除できます。これは非常によく使うテクニックです。

' ハイフンを削除
=SUBSTITUTE(A1,"-","")
→ 「090-1234-5678」→「09012345678」

' カンマを削除
=SUBSTITUTE(A1,",","")
→ 「1,234,567」→「1234567」

' スラッシュを削除
=SUBSTITUTE(A1,"/","")
→ 「2024/01/15」→「20240115」

N番目の出現箇所だけ置換（第4引数）

SUBSTITUTE関数の第4引数を指定すると、N番目に出現する検索文字列だけを置換できます。

' A1に「Excel-VBA-マクロ-入門」が入っている場合

' 全てのハイフンを置換（第4引数なし）
=SUBSTITUTE(A1,"-","_")
→ 「Excel_VBA_マクロ_入門」

' 2番目のハイフンだけ置換
=SUBSTITUTE(A1,"-","★",2)
→ 「Excel-VBA★マクロ-入門」

' 1番目のハイフンだけ置換
=SUBSTITUTE(A1,"-","／",1)
→ 「Excel／VBA-マクロ-入門」

第4引数は省略すると全ての出現箇所が置換されます。特定の1箇所だけ変えたい場合に便利です。

複数の文字を一度に置換（ネスト）

SUBSTITUTE関数をネスト（入れ子）にすることで、複数の文字を一度に置換できます。

' ハイフン、カッコ、スペースを全て削除（電話番号の正規化）
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"(",""),")","")
→ 「(03)1234-5678」→「0312345678」

' 全角数字を半角に変換（0～9）
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
  SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
  A1,"０","0"),"１","1"),"２","2"),"３","3"),"４","4"),
  "５","5"),"６","6"),"７","7"),"８","8"),"９","9")

ネストが深くなると数式が読みにくくなります。3つ以上のネストが必要な場合は、作業列を使って段階的に処理するか、後述のVBAマクロを検討しましょう。

期間限定でChatGPT✖︎Googleスプレットシートのコンテンツ配布中！

LINEでExcelを気軽に学べる

しんたろ。
しんたろ。
Excel歴10年以上 → アプリ開発者
Profile
大手メーカーに15年以上勤務。製造部門で海外拠点の立ち上げ支援や、現場責任者として採算管理・納期管理・設備オペレートを経験。 2023年にDX人材育成プログラム第1期生として活動後、現在は製造現場のスケジュール運用を支えるアプリの企画・開発をメインに活動中。工程表示表作成の内製化SaaSを構築し、年間1,300万円のコスト改善を実現。 Excelによる業務改善で年間240時間の残業削減を達成した経験を活かし、ブログやSNSでも情報発信しています。
プロフィールを読む
YouTubeLINEContact
この操作でよくある疑問

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

A: はい、SUBSTITUTE関数は大文字・小文字を厳密に区別します。「abc」を指定しても「ABC」は置換されません。大文字小文字を無視して置換したい場合は、一度UPPER関数やLOWER関数で統一してからSUBSTITUTEを適用するか、VBAのReplace関数（vbTextCompare指定）を使う方法があります。

実践編｜データクリーニングのテクニック集

VLOOKUP前のデータ整形（TRIM+SUBSTITUTE）

VLOOKUP関数で「データがあるはずなのに#N/Aエラーになる」場合、原因の多くは検索値と参照先のデータ形式が一致していないことです。TRIM関数とSUBSTITUTE関数で事前にデータを整形しましょう。

' VLOOKUPの検索値をクリーニングしてから検索
=VLOOKUP(TRIM(SUBSTITUTE(A1,"　","")),マスタ!A:B,2,FALSE)

' または、あらかじめ作業列でクリーニング
' B列にクリーニング済みデータを用意
=TRIM(CLEAN(SUBSTITUTE(A1,"　","")))

' クリーニング済みデータでVLOOKUP
=VLOOKUP(B1,マスタ!A:B,2,FALSE)

ポイント: マスタデータ側もクリーニングが必要な場合があります。検索値と参照先の両方にTRIMを適用するのがベストプラクティスです。

改行コードの除去（CHAR(10)の置換）

セル内改行（Alt+Enter）を含むデータをCSV出力すると、データが崩れることがあります。SUBSTITUTE関数とCHAR関数を使って改行コードを除去しましょう。

' セル内の改行を削除
=SUBSTITUTE(A1,CHAR(10),"")

' 改行をスペースに置換（内容を保持）
=SUBSTITUTE(A1,CHAR(10)," ")

' 改行 + 余分なスペースも整形
=TRIM(SUBSTITUTE(A1,CHAR(10)," "))

' Macの場合はCHAR(13)も考慮
=SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),""),CHAR(10),"")

補足: ExcelのセルではCHAR(10)が改行コードです。Webからコピーしたデータの場合、CHAR(13)（キャリッジリターン）が含まれていることもあるため、両方を処理すると確実です。

電話番号のハイフン統一

顧客データで電話番号の表記がバラバラ（ハイフンあり/なし、全角/半角）になっていることはよくあります。SUBSTITUTE関数で統一しましょう。

' ハイフンを全て削除して数字だけにする
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),"−",""),"ー","")

' 全角ハイフン → 半角ハイフンに統一
=SUBSTITUTE(SUBSTITUTE(A1,"−","-"),"ー","-")

' カッコとスペースも含めて正規化
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
  SUBSTITUTE(A1,"-",""),"−",""),"ー",""),"(",""),")","")

電話番号を数字だけにしておくと、重複チェックや検索が正確に行えます。

CSV取り込みデータの一括クリーニング

CSVファイルをExcelに取り込むと、ダブルクォーテーション（”）の残留、先頭のBOM文字、余分なスペースなどの問題が発生しがちです。以下の数式で一括クリーニングできます。

' CSVデータの一括クリーニング（ダブルクォート + スペース + 制御文字）
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A1,"""",""),"　","")))

' BOM（バイトオーダーマーク）の除去
=IF(CODE(LEFT(A1,1))=65279,MID(A1,2,LEN(A1)-1),A1)

' 上記を組み合わせた完全版クリーニング数式
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(
  IF(CODE(LEFT(A1,1))=65279,MID(A1,2,LEN(A1)-1),A1),
  """",""),"　","")))

大量のデータを処理する場合は、作業列を使って段階的にクリーニングすると、どの処理でデータが変わったか追跡しやすくなります。

筆者の実務Tips

データクリーニングの現場で最も厄介なのが「見た目は同じなのにVLOOKUPが一致しない」問題です。筆者の経験では、原因の大半は①末尾の半角スペース、②全角ハイフンと半角ハイフンの混在、③CHAR(160)のノーブレークスペースの3つに集約されます。CSVインポート後に「=TRIM(SUBSTITUTE(SUBSTITUTE(A1,”　”,””),”−”,”-“))」を作業列として全データに適用するだけで、マッチ率が平均95%以上に改善します。この「クリーニング3点セット」は社内テンプレートとして共有しておくと、チーム全体の生産性が上がりますよ。

TRIM・SUBSTITUTEと他の関数の組み合わせ

SUBSTITUTE × LEN で特定文字の出現回数を数える

SUBSTITUTE関数とLEN関数を組み合わせると、セル内の特定文字の出現回数を数えることができます。

' A1に含まれる「,」（カンマ）の数を数える
=(LEN(A1)-LEN(SUBSTITUTE(A1,",","")))/LEN(",")

' 具体例: A1 = "東京,大阪,名古屋,福岡"
→ 結果: 3（カンマが3つ）

' 複数文字の出現回数も数えられる
' A1に含まれる「Excel」の出現回数
=(LEN(A1)-LEN(SUBSTITUTE(A1,"Excel","")))/LEN("Excel")

仕組み: 元の文字列の長さから、対象文字を削除した後の長さを引くと、削除された文字の総文字数がわかります。それを対象文字の長さで割れば出現回数になります。

TRIM × VALUE で文字列を数値に変換

CSVやWebから取り込んだ数値データが「文字列として認識」されている場合、TRIM関数とVALUE関数を組み合わせて数値に変換できます。

' 文字列として格納された数値を数値に変換
=VALUE(TRIM(A1))

' カンマ区切りの数値を数値に変換
=VALUE(SUBSTITUTE(TRIM(A1),",",""))

' 通貨記号を除去して数値に変換
=VALUE(SUBSTITUTE(SUBSTITUTE(TRIM(A1),"¥",""),",",""))

セルの左上に緑の三角マーク（エラーインジケーター）が表示されている場合、その数値は文字列として格納されています。この数式で正しい数値に変換できます。

SUBSTITUTE × LEFT/MID/RIGHT で高度な抽出

SUBSTITUTE関数をLEFT/MID/RIGHT関数と組み合わせると、区切り文字を利用した高度な文字列抽出が可能です。

' メールアドレスからドメイン部分を抽出
' A1 = "user@example.com"
=MID(A1,FIND("@",A1)+1,LEN(A1))
→ 「example.com」

' 区切り文字で2番目の要素を抽出（A1 = "東京-大阪-名古屋"）
=MID(SUBSTITUTE(A1,"-",REPT(" ",100)),101,100)
→ 「大阪」（前後にスペースが入るのでTRIMで除去）

' 完全版: N番目の要素を抽出
=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",100)),(2-1)*100+1,100))
→ 2番目の要素「大阪」をスペースなしで取得

この「REPT+MID方式」は、Excelで文字列をスプリットする定番テクニックです。区切り文字を大量のスペースに置換し、MID関数で該当区間を切り出す仕組みです。

VBAで文字列置換を自動化

大量のデータに対して繰り返しクリーニングを行う場合は、VBAマクロで自動化すると効率的です。

VB
VBA データクリーニング デモ
Sub BasicCleanup() Dim txt As String txt = “　 田中　　太郎 　” txt = Replace(txt, “　”, ” “) ‘ 全角→半角 txt = Trim(txt) ‘ 前後除去 Do While InStr(txt, ” “) > 0 ‘ 連続→1つ txt = Replace(txt, ” “, ” “) Loop Debug.Print txt End Sub
▶ 実行結果
Before: “　 田中　　太郎 　”
After: “田中 太郎”
Sub RegexClean() Dim re As Object, txt As String Set re = CreateObject(“VBScript.RegExp”) txt = ” (03) 1234-5678 ” re.Global = True re.Pattern = “[-()\s　]+” txt = re.Replace(txt, “”) Debug.Print txt End Sub
▶ 実行結果
Before: ” (03) 1234-5678 “
After: “0312345678”　← 数字のみに正規化
Sub CleanAllSheets() Dim ws As Worksheet, cell As Range Dim cnt As Long For Each ws In ThisWorkbook.Worksheets For Each cell In ws.UsedRange If VarType(cell.Value) = vbString Then Dim v As String: v = cell.Value v = Replace(v, “　”, ” “) v = Trim(v) If v <> cell.Value Then cell.Value = v: cnt = cnt + 1 End If End If Next Next MsgBox cnt & “セル修正完了” End Sub
▶ 実行イメージ
📝 全シートのテキストセルを自動スキャン
🔨 全角スペース変換 + 前後トリム
修正されたセル数をメッセージで表示

VBAのReplace関数とTrim関数

VBAにはワークシート関数と対応するReplace関数とTrim関数があります。

Sub BasicCleanup()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range

    Set ws = ActiveSheet
    Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)

    For Each cell In rng
        If cell.Value <> "" Then
            ' 全角スペースを半角に変換
            cell.Value = Replace(cell.Value, "　", " ")
            ' 前後のスペースを削除
            cell.Value = Trim(cell.Value)
            ' 連続スペースを1つに（VBAのTrimは前後のみ）
            Do While InStr(cell.Value, "  ") > 0
                cell.Value = Replace(cell.Value, "  ", " ")
            Loop
        End If
    Next cell

    MsgBox rng.Rows.Count & "行のクリーニングが完了しました", vbInformation
End Sub

注意: VBAのTrim関数は前後のスペースのみ削除し、ワークシートのTRIM関数のように単語間の連続スペースを1つにまとめる機能はありません。上記コードではDo Whileループで連続スペースを処理しています。

正規表現（RegExp）による高度な置換

VBAでは正規表現を使った高度な文字列置換が可能です。SUBSTITUTE関数では対応できないパターンマッチングに威力を発揮します。

Sub RegexReplace()
    Dim regEx As Object
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range

    Set regEx = CreateObject("VBScript.RegExp")
    Set ws = ActiveSheet
    Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)

    ' 正規表現の設定
    regEx.Global = True
    regEx.IgnoreCase = True

    For Each cell In rng
        If cell.Value <> "" Then
            Dim txt As String
            txt = cell.Value

            ' 連続する空白文字（半角・全角・タブ）を1つのスペースに
            regEx.Pattern = "[\s\x{3000}]+"
            txt = regEx.Replace(txt, " ")

            ' 前後の空白を除去
            regEx.Pattern = "^\s+|\s+$"
            txt = regEx.Replace(txt, "")

            ' 電話番号を統一形式に（例: 09012345678 → 090-1234-5678）
            regEx.Pattern = "^(0\d{1,3})[\s\-]*(\d{1,4})[\s\-]*(\d{4})$"
            txt = regEx.Replace(txt, "$1-$2-$3")

            cell.Value = txt
        End If
    Next cell

    MsgBox "正規表現によるクリーニングが完了しました", vbInformation
End Sub

正規表現を使えば、「大文字小文字を区別しない置換」「パターンに一致する文字列の置換」など、SUBSTITUTE関数では実現できない高度な処理が可能になります。

全シート一括クリーニングマクロ

ブック内の全シートに対して一括でデータクリーニングを実行するマクロです。CSVインポート後の定期処理に最適です。

Sub CleanAllSheets()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim cleanCount As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For Each ws In ThisWorkbook.Worksheets
        ' 使用範囲を取得
        If WorksheetFunction.CountA(ws.UsedRange) > 0 Then
            Set rng = ws.UsedRange

            For Each cell In rng
                If VarType(cell.Value) = vbString And cell.Value <> "" Then
                    Dim original As String
                    original = cell.Value

                    ' 制御文字を除去（CLEAN関数相当）
                    Dim i As Long, cleaned As String
                    cleaned = ""
                    For i = 1 To Len(cell.Value)
                        If Asc(Mid(cell.Value, i, 1)) >= 32 Then
                            cleaned = cleaned & Mid(cell.Value, i, 1)
                        End If
                    Next i

                    ' 全角スペースを半角に変換
                    cleaned = Replace(cleaned, "　", " ")
                    ' 前後のスペースを除去
                    cleaned = Trim(cleaned)
                    ' 連続スペースを1つに
                    Do While InStr(cleaned, "  ") > 0
                        cleaned = Replace(cleaned, "  ", " ")
                    Loop

                    If original <> cleaned Then
                        cell.Value = cleaned
                        cleanCount = cleanCount + 1
                    End If
                End If
            Next cell
        End If
    Next ws

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    MsgBox "全シートのクリーニングが完了しました。" & vbCrLf & _
           "修正セル数: " & cleanCount, vbInformation
End Sub

使い方: Alt+F11でVBAエディタを開き、標準モジュールに上記コードを貼り付けて実行します。処理前にブックのバックアップを取ることをおすすめします。

よくある質問（FAQ）

Q: TRIM関数で全角スペースが削除されないのはなぜ？

A: ExcelのTRIM関数は半角スペース（ASCII 32）のみを対象としています。全角スペースは=SUBSTITUTE(A1,”　”,””)で削除するか、=TRIM(SUBSTITUTE(A1,”　”,” “))で半角に変換してからTRIMを適用してください。

Q: SUBSTITUTE関数で大文字・小文字を区別しないで置換するには？

A: SUBSTITUTE関数は大文字・小文字を区別します。区別しない置換にはVBAのReplace関数か、=SUBSTITUTE(UPPER(A1),”ABC”,”xyz”)のように一旦大文字（または小文字）に統一してから置換する方法があります。ただし、この方法では元の大文字小文字の情報が失われる点に注意してください。

Q: 「検索と置換」機能とSUBSTITUTE関数の違いは？

A: 「検索と置換」（Ctrl+H）は元データを直接変更しますが、SUBSTITUTE関数は別セルに結果を出力するため元データを残せます。データの変更履歴を追跡したい場合や、元データを保持したい場合はSUBSTITUTE関数が安全です。一方、単純に一括置換したいだけなら「検索と置換」の方が手軽です。

Q: SUBSTITUTE関数で改行を置換するには？

A: =SUBSTITUTE(A1,CHAR(10),””)で改行コードを削除できます。CHAR(10)はExcelのセル内改行コード（LF）です。Webからコピーしたデータの場合はCHAR(13)（CR）も含まれていることがあるため、=SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),””),CHAR(10),””)とすると確実です。

Q: スプレッドシートでもTRIM/SUBSTITUTE関数は使えますか？

A: はい、GoogleスプレッドシートでもTRIM関数・SUBSTITUTE関数はExcelと同じ構文で使えます。ただし、GoogleスプレッドシートのTRIM関数は全角スペースも削除する点がExcelと異なります。Excel→スプレッドシートへの移行時に動作の違いに注意してください。

まとめ

TRIM関数とSUBSTITUTE関数のポイントをおさらいしましょう。

関数主な用途覚えておくべきポイント
TRIM余分なスペースの削除半角スペースのみ対応。全角はSUBSTITUTEで処理
SUBSTITUTE文字列の置換・削除大文字小文字を区別。第4引数でN番目のみ置換可
CLEAN制御文字の除去TRIMと組み合わせて使うのが定番
REPLACE位置指定の文字列置換固定位置の置換に使用。文字列検索はSUBSTITUTE

実務での使い分け:

  • スペース除去 → TRIM関数（全角はSUBSTITUTEと併用）
  • 特定文字の置換・削除 → SUBSTITUTE関数
  • 外部データの一括整形 → TRIM(CLEAN(SUBSTITUTE(A1,”　”,””)))
  • 大量データの定期処理 → VBAマクロ

まずは=TRIM(CLEAN(SUBSTITUTE(A1,”　”,””)))を「データクリーニングの万能数式」として覚えておけば、ほとんどの場面で対応できます。VLOOKUPがうまくいかないときは、まずこの数式でデータを整形してみてください。

Excelスキルを活かしてSNS運用も自動化しませんか？

Excelの関数をマスターしたあなたなら、Googleスプレッドシート×GAS（Google Apps Script）を使ったSNS自動化ツールもすぐに使いこなせます。X（Twitter）やThreadsの予約投稿を、月額0円で自動化する方法をまとめています。

→ SNS予約投稿ツール完全ガイド｜無料＆買い切りツールを徹底比較

関連記事

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

ABOUT ME
しんたろ。
しんたろ。
Excel歴10年以上 → アプリ開発者
YouTubeLINEContact
大手メーカーに15年以上勤務。製造部門で海外拠点の立ち上げ支援や、現場責任者として採算管理・納期管理・設備オペレートを経験。 2023年にDX人材育成プログラム第1期生として活動後、現在は製造現場のスケジュール運用を支えるアプリの企画・開発をメインに活動中。工程表示表作成の内製化SaaSを構築し、年間1,300万円のコスト改善を実現。 Excelによる業務改善で年間240時間の残業削減を達成した経験を活かし、ブログやSNSでも情報発信しています。
Recommend
こんな記事も読まれています！
記事URLをコピーしました