【VBA】データクリーニング（空白除去・改行統一・全半角変換）を一括実行する方法（コピペOK）

この記事でできること

VBAでデータクリーニング（空白除去・改行統一・全角→半角変換・余分なスペース除去）を一括実行できるようになる。

• 対象：外部データの貼り付けやCSV取込後の表記ゆれに困っている人
• 所要時間：コピペ → 実行まで5分

どんな場面で使う？

• CSV取り込み後の全角スペースや改行コードの混在をまとめて除去したいとき
• 表記ゆれ（全角半角・大文字小文字）を一括で統一したいとき
• データ分析の前処理として空白トリム・不要文字除去を自動化したいとき
• 毎月のデータ取り込み時にクリーニングをルーティン化したいとき

—

完成イメージ（Before / After）

Before（クリーニング前）

※ 　 = 全角スペース、末尾の空白、改行コードの混在

After（クリーニング後）

前後の空白除去、全角→半角変換、改行統一、連続スペース除去がまとめて完了する。

—

導入

自分も基幹システムからエクスポートしたデータをExcelに貼り付けると、全角スペースが混ざっていたり、改行コードがバラバラだったり、VLOOKUPが一致しない原因が「見えない空白」だったりして、地味にストレスだった。

VBAでクリーニング処理をまとめてからは、貼り付けた直後にマクロを実行するだけでデータが綺麗になる。VLOOKUPの不一致で悩む時間がなくなった。

この記事で、同じ悩みを持つ人がデータの前処理をサクッと自動化できるようになればうれしい。

前提条件

– Excel 2016以降 / Microsoft 365

– Windows 10/11

– ファイルは .xlsm（マクロ有効ブック）で保存

– コードは 標準モジュール に貼り付け

– 実行方法：Alt + F8 →マクロ選択 → 実行

空白行の削除もあわせて行いたい場合は、空白行・空白セルを一括で削除する方法も参考にしてください。

—

手順

1. Excelファイルを .xlsm で保存する
• ファイル → 名前を付けて保存 → ファイルの種類を「Excelマクロ有効ブック (*.xlsm)」に変更
• VBEを開く
• Alt + F11 キーを押す
• 標準モジュールを挿入する
• VBE画面の左側「VBAProject」を右クリック → 挿入 → 標準モジュール
• コードを貼り付ける
• 下記のコードをコピーして貼り付ける
• クリーニング対象のシートを開いた状態でマクロを実行する
• Alt + F8 → マクロ名を選択 → 実行

実行前に必ずバックアップを取ってください。 セルの値が上書きされるため、元に戻す（Ctrl+Z）が効きません。

—

基本コード（Trim + Clean で最低限のクリーニング）

まずはこれで動く最小限のコード。アクティブシートの使用範囲にある文字列セルに対して、Trim（前後の半角スペース除去）と Clean（制御文字除去）を適用する。

Sub 基本クリーニング()
    Dim rng As Range
    Dim cell As Range

    ' アクティブシートの使用範囲を取得
    Set rng = ActiveSheet.UsedRange

    For Each cell In rng
        If VarType(cell.Value) = vbString Then
            ' Trim: 前後の半角スペース除去
            ' Clean: 制御文字（改行等）を除去
            cell.Value = Trim(Application.WorksheetFunction.Clean(cell.Value))
        End If
    Next cell

    MsgBox "基本クリーニングが完了しました。", vbInformation
End Sub

ポイント

• VBAの Trim は前後の半角スペースだけ除去する（全角スペースは残る）
• WorksheetFunction.Clean で制御文字（改行含む）を除去できる
• 全角スペースや全半角変換には対応していないので、実務では次の実務版を使う

—

実務版コード（複数処理を組み合わせたクリーニング関数）

自分はこの実務版をクリーニング用の「いつものマクロ」として使っている。基幹システムからのエクスポートデータ、CSV取込データ、Webからのコピペデータ、どんなデータでもまずこれを実行する。データ整形の前処理が安定してから、VLOOKUPの不一致で悩むことがほぼなくなった。

Sub データクリーニング一括実行()
    '================================================
    ' アクティブシートの使用範囲に対して
    ' 複数のクリーニング処理を一括実行する
    '================================================

    Dim rng As Range
    Dim cell As Range
    Dim val As String

    Set rng = ActiveSheet.UsedRange

    ' 画面更新を停止して高速化
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For Each cell In rng
        If VarType(cell.Value) = vbString Then
            val = cell.Value

            ' 1. 改行コードを統一（vbCr, vbCrLf → vbLf）
            val = Replace(val, vbCrLf, vbLf)
            val = Replace(val, vbCr, vbLf)

            ' 2. 連続する改行を1つにまとめる
            Do While InStr(val, vbLf & vbLf) > 0
                val = Replace(val, vbLf & vbLf, vbLf)
            Loop

            ' 3. 全角スペースを半角スペースに変換
            val = Replace(val, "　", " ")

            ' 4. 連続する半角スペースを1つにまとめる
            Do While InStr(val, "  ") > 0
                val = Replace(val, "  ", " ")
            Loop

            ' 5. 前後の半角スペースを除去
            val = Trim(val)

            ' ---- ここが追加：StrConvで全角英数→半角英数に変換 ----
            ' 6. 全角英数カナ → 半角英数カナ に変換
            val = StrConv(val, vbNarrow)
            ' ---- 追加ここまで ----

            cell.Value = val
        End If
    Next cell

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    MsgBox "データクリーニングが完了しました。", vbInformation
End Sub

処理の流れ

StrConv の注意点: vbNarrow はカタカナも半角にします。半角カタカナを避けたい場合は、全角⇔半角を一括変換してデータを統一する方法で英数字だけを変換する方法を紹介しています。

—

落とし穴

自分も最初、StrConv(val, vbNarrow) を全セルに適用したら、カタカナまで半角になって「ﾄｳｷｮｳ」みたいな表示になった。上司に出す資料でそれをやったときは冷や汗をかいた。

—

VBAのTrimで全角スペースが除去されないときの対処法

「Trimを実行したのに先頭の空白が消えない」という場合、原因はVBAのTrim関数が半角スペースしか除去しないことだ。全角スペース（Chr(12288)）はReplace関数で別途除去する。Replace(Trim(値), Chr(12288), "") のように組み合わせると半角・全角の両方に対応できる。

VBAのStrConvで変換結果がおかしいときの対処法

「全角→半角変換をしたらカタカナまで半角になった」という場合、原因はvbNarrowが全角カタカナも半角カタカナに変換するためだ。数字・英字だけ半角にしたい場合は、正規表現や文字コード判定で対象を限定してから変換する。

FAQ

Q1. 特定の列だけクリーニングしたい

Set rng = ActiveSheet.UsedRange の行を変更すればOKです。例えばA列だけなら：

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

Q2. 全角カタカナはそのままで、英数字だけ半角にしたい

StrConv(val, vbNarrow) の代わりに、全角⇔半角を一括変換してデータを統一する方法で紹介している方法を使ってください。正規表現で全角英数字だけを抽出して変換する方法があります。自分も最初これがわからなくて調べた結果、英数字だけ変換する関数を別途作る方法に落ち着いた。

Q3. クリーニング後にVLOOKUPの一致率は上がる？

上がります。VLOOKUPが一致しない原因の多くは「見えない空白」「全角半角の違い」「改行コードの混在」です。このマクロでそれらを統一すれば、一致率は大幅に改善します。文字列の一括置換と組み合わせるとさらに効果的です。セルの文字列を一括置換する方法も参考にしてください。

Q4. 数値セルや日付セルには影響しない？

影響しません。VarType(cell.Value) = vbString の条件で文字列セルだけを対象にしています。数値・日付・数式・空白セルはスキップされます。

Q5. 定期的に自動実行したい

ブックを開いたときに自動実行する方法や、ボタンに割り当てる方法があります。定期的なデータ取込の後に毎回実行するなら、取込マクロの末尾にこのクリーニング処理を呼び出すのが実務的です。

—

—

まとめ

この記事では、VBAでデータクリーニング（空白除去・改行統一・全半角変換）を一括実行する方法を紹介した。

• 基本コード：Trim + Clean で最低限の空白・制御文字を除去
• 実務版コード：改行統一 → スペース統一 → 前後空白除去 → 全角→半角変換を一括実行

VLOOKUPの不一致やデータの表記ゆれに悩んでいるなら、まずこのクリーニングマクロを実行してみてほしい。データが綺麗になるだけで、後続の集計・分析がスムーズに進む。

—

次にやりたくなること

• 全角と半角を一括変換してデータを統一する方法: StrConvを使った全角半角変換を詳しく知りたい場合
• セルの文字列を一括置換する方法: Replaceを使った文字列の一括置換を知りたい場合
• CSVファイルをExcelに正しく取り込む方法: クリーニングの前段階であるCSV取り込みを正確に行いたい場合

データクリーニングができたら、次はこのあたりが気になるはず。

• 全角⇔半角の変換をもっと細かく制御したい → 全角⇔半角を一括変換してデータを統一する方法で、カタカナを除外した変換や半角→全角の逆変換も紹介している
• 空白行を一括で削除したい → 空白行・空白セルを一括で削除する方法で、クリーニング後の空行除去をまとめて実行できる
• 特定の文字列をまとめて置換したい → セルの文字列を一括置換する方法で、表記ゆれの統一（例：「株式会社」→「(株)」）を自動化できる
• セル内の改行をもっと細かく操作したい → セル内の改行を追加・削除・分割する方法で、改行の追加・分割・行ごとの取り出しも紹介している