Contents
この記事でできること
- VBAで指定範囲の文字列を一括置換できる
- 複数の置換パターンをリストで管理して一括実行できる
- 条件付き置換（特定列の値で判定してから置換）ができる
- 正規表現を使ったパターン置換ができる
対象: Excel 2016以降 / Microsoft 365、Windows 10/11
完成イメージ（Before / After）
Before（旧コード）:
| A | B | |
|---|---|---|
| 1 | 会社名 | ステータス |
| 2 | 株式会社ABC商事 | 変更 |
| 3 | 有限会社DEF工業 | 変更 |
| 4 | 株式会社GHI物産 | 据置 |
| 5 | 有限会社JKL製作所 | 変更 |
After（新コード）:
| A | B | |
|---|---|---|
| 1 | 会社名 | ステータス |
| 2 | (株)ABC商事 | 変更 |
| 3 | (有)DEF工業 | 変更 |
| 4 | 株式会社GHI物産 | 据置 |
| 5 | (有)JKL製作所 | 変更 |
B列が「変更」の行だけ置換される。「据置」の行はそのまま。
取引先コードの体系が変わって、500件のデータを一括置換する必要があった。Ctrl+Hで1パターンずつ置換していたら、3パターン目で見落としが発生。上司に提出した後に指摘されて、全データを再チェックする羽目になった。正直しんどかった。
VBAで置換リストを作ってからは状況が変わった。4パターンの置換が3秒で完了。見落としもゼロ。置換リストをコードに書いておけば、次回も同じ処理を1クリックで再現できる。
複数パターンの置換に時間がかかっている人に、この一括置換を知ってほしい。まずはRange.Replaceの最小版から試して、動くことを確認するところから始めよう。
置換リストを1回作れば、何パターンでもミスなく3秒で一括置換できる。
実行前の準備
バックアップを取る
VBAで実行した置換はCtrl+Zで元に戻せない。 これが手動の置換との最大の違い。実行前に必ずファイルのコピーを取るか、対象シートをコピーしてバックアップシートを作っておくこと。
テストデータで事前確認する
いきなり本番データに対して実行しない。テスト用のシートを作り、少量のデータで置換結果を確認してから本番データに適用する。
Excelをマクロ有効ブック（.xlsm）で保存する
拡張子が .xlsx のままだとマクロは保存できない。
- 「ファイル」→「名前を付けて保存」
- ファイルの種類を「Excelマクロ有効ブック (*.xlsm)」に変更
- 保存
手順（コピペ → 実行まで約5分）
VBE（コードを書く画面）を開く
- Excelで
Alt + F11を押す - VBE（Visual Basic Editor）が開く
標準モジュールを挿入する
- VBEのメニュー →「挿入」→「標準モジュール」
- 白い画面（コードウィンドウ）が表示される
コードを貼り付けて実行する
- コードウィンドウに、下のコードをそのままコピペする
- コード内の書き換えポイント（★マーク）を自分の環境に合わせて変更する
Alt + F8→ マクロ名を選んで「実行」
コード（最小版）– Range.Replaceでシンプルな一括置換
指定列の文字列を1パターンで一括置換する。ExcelのCtrl+Hと同じ動作をVBAで実行する最もシンプルな形。まずはこれで動くことを確認する。
'============================================================
' ■ セルの文字列を一括置換する（最小版）
' → Range.ReplaceでExcelの置換機能と同じ動作
' → 指定列の文字列を一括で置き換える
'============================================================
Sub 文字列を一括置換する()
Dim ws As Worksheet
Set ws = ActiveSheet
'--- ★書き換えポイント ---
' What : 検索する文字列
' Replacement : 置換後の文字列
' 対象列 : ws.Columns("A") を変更すれば対象列を変えられる
ws.Columns("A").Replace _
What:="株式会社", _
Replacement:="(株)", _
LookAt:=xlPart, _
MatchCase:=False
'--- ★ここまで ---
MsgBox "置換が完了しました。"
End Sub
書き換えポイント
| 引数 | 説明 | 初期値 | 例 |
|---|---|---|---|
What |
検索する文字列 | "株式会社" |
"旧コード" |
Replacement |
置換後の文字列 | "(株)" |
"新コード" |
ws.Columns("A") |
対象列 | A列 | ws.Range("A2:D100") |
LookAt |
一致方法 | xlPart（部分一致） |
xlWhole（完全一致） |
MatchCase |
大文字小文字の区別 | False（区別しない） |
True（区別する） |
Range.Replaceの主要引数
| 引数 | 値 | 説明 |
|---|---|---|
LookAt |
xlPart |
部分一致（セルの一部に含まれていれば置換） |
LookAt |
xlWhole |
完全一致（セルの値が完全に一致する場合のみ置換） |
MatchCase |
False |
大文字小文字を区別しない |
MatchCase |
True |
大文字小文字を区別する |
LookIn |
xlValues |
値のみを対象（数式は置換しない） |
LookIn |
xlFormulas |
数式も含めて置換（省略時のデフォルト） |
コードの流れ
- シート取得: ActiveSheetを変数に格納する
- 置換実行: Range.ReplaceでA列全体の「株式会社」を「(株)」に一括置換する
- 完了通知: MsgBoxで完了を表示する
テスト方法: A列に「株式会社ABC」「株式会社DEF」など数件のテストデータを入力し、マクロを実行する。「(株)ABC」「(株)DEF」に置き換わっていれば成功。最終行の取得方法（データの最終行・最終列を正確に取得する方法）を知っておくと、対象範囲の指定に役立つ。
コード（実務版）– 置換リストで複数パターンを一括処理
実務では1パターンだけの置換では足りない。「株式会社→(株)」「有限会社→(有)」「全角スペース→半角スペース」「連続半角スペース→半角スペース1個」のように、複数パターンをまとめて処理したい。
自分は取引先マスタの整理で、4パターンの置換を毎月やっていた。Ctrl+Hで1つずつ処理すると20分かかっていた上に、パターンの漏れが月1回は発生していた。この実務版コードに切り替えてからは3秒で完了。パターンの追加もArray配列に1行追加するだけ。
実務版では以下の機能を追加する：
- 置換リスト: Array配列で複数の置換パターンを管理
- ループ実行: For Eachで全パターンを一括処理
- 最終行取得: Cells.End(xlUp).Rowで動的に対象範囲を決定（最終行取得の詳細）
- 高速化: Application.ScreenUpdating = Falseで画面更新を停止
- 完了通知: 実行したパターン数をMsgBoxで表示
複数シートに同じ置換を適用したい場合は、複数シートに同じ処理を一括実行と組み合わせるとさらに効率的。
'============================================================
' ■ 複数パターンを一括置換する（実務版）
' → 置換リスト（Array）で複数パターンを管理
' → For Eachループで全パターンを一括実行
' → 最終行を動的取得して対象範囲を自動調整
'============================================================
Sub 複数パターンを一括置換する()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim cnt As Long
'--- ★書き換えポイント ---
' 置換対象のシート名を変更する
Set ws = Worksheets("データ")
'--- ★ここまで ---
'--- 最終行を取得（A列基準）
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
'--- ★書き換えポイント ---
' 置換リスト（検索文字列, 置換文字列）
' パターンを追加する場合は Array("検索", "置換") を追加する
Dim replaceList As Variant
replaceList = Array( _
Array("株式会社", "(株)"), _
Array("有限会社", "(有)"), _
Array(" ", " "), _
Array(" ", " ") _
)
'--- ★ここまで ---
Application.ScreenUpdating = False
'--- 各パターンで置換を実行
Dim pair As Variant
For Each pair In replaceList
ws.Range("A2:D" & lastRow).Replace _
What:=pair(0), _
Replacement:=pair(1), _
LookAt:=xlPart, _
MatchCase:=False
cnt = cnt + 1
Next pair
Application.ScreenUpdating = True
MsgBox cnt & " パターンの置換が完了しました。"
End Sub
書き換えポイント
| 変数 | 説明 | 初期値 |
|---|---|---|
Worksheets("データ") |
置換対象のシート名 | "データ" |
replaceList |
置換パターンの配列 | 4パターン |
ws.Range("A2:D" & lastRow) |
置換対象の範囲 | A2〜D列の最終行 |
パターンを追加する場合: replaceList の配列に Array("検索文字列", "置換文字列") を1行追加するだけ。カンマ区切りと行末の _（行継続文字）を忘れずに。
コードの流れ
- シート・最終行取得: 対象シートとA列の最終行を取得する
- 置換リスト定義: Array配列で検索文字列と置換文字列のペアを管理する
- ループ実行: For Eachで各パターンをRange.Replaceで一括置換する
- 完了通知: 実行したパターン数をMsgBoxで表示する
置換リストをArray配列で管理すれば、パターンの追加・変更・削除がコードの1行で済む。
VBA Replace関数でセル単位の条件付き置換
Range.Replaceは範囲全体に対する一括置換。「B列が”変更”の行だけA列を置換したい」といった条件付き置換には、VBA Replace関数を使ってセル単位で処理する。
書式変更（セルの書式を一括変更する方法）と組み合わせれば、置換した行だけ色を変えて目視確認することもできる。
'============================================================
' ■ セル単位で条件付き置換する
' → VBA Replace関数で1セルずつ置換
' → If文で条件分岐（B列の値で判定）
' → 条件に合致する行だけを選択的に置換
'============================================================
Sub セル単位で条件付き置換する()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim cnt As Long
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
Dim cellVal As String
cellVal = ws.Cells(i, 1).Value
'--- ★書き換えポイント ---
' 条件: B列が「変更」の行だけ置換する
' 置換: A列の「旧」を「新」に置き換える
If ws.Cells(i, 2).Value = "変更" Then
ws.Cells(i, 1).Value = Replace(cellVal, "旧", "新")
cnt = cnt + 1
End If
'--- ★ここまで ---
Next i
MsgBox cnt & " 件のセルを置換しました。"
End Sub
Range.Replace vs VBA Replace関数
| 比較項目 | Range.Replace | VBA Replace関数 |
|---|---|---|
| 対象 | セル範囲 | 文字列変数 |
| 条件分岐 | 不可 | If文で可能 |
| 処理速度 | 高速（一括処理） | 遅い（1セルずつ） |
| 正規表現 | 不可 | 不可 |
| 推奨ケース | 単純な一括置換 | 条件付き置換 |
使い分けの目安: 条件なしの一括置換ならRange.Replace。条件付き（特定列の値で判定、特定行だけ対象など）ならVBA Replace関数。
補足：正規表現で高度な置換（VBScript.RegExp）
Range.ReplaceやVBA Replace関数では対応できない「パターンマッチ」の置換は正規表現を使う。たとえば「3桁-4桁の数字（郵便番号）をマスキングしたい」「電話番号のハイフンを統一したい」といったケースで有効。
正規表現はCreateObject(“VBScript.RegExp”)で参照設定なしで使える。
'============================================================
' ■ 正規表現で置換する（補足・高度）
' → VBScript.RegExpでパターンマッチ置換
' → 参照設定不要（CreateObject使用）
' → 例: 郵便番号パターン（3桁-4桁）をマスキング
'============================================================
Sub 正規表現で置換する()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim reg As Object
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
'--- 正規表現オブジェクトを作成
Set reg = CreateObject("VBScript.RegExp")
reg.Global = True
reg.IgnoreCase = True
'--- ★書き換えポイント ---
' Pattern : 正規表現パターン
' 例: \d{3}-\d{4} → 3桁-4桁の数字（郵便番号パターン）
reg.Pattern = "\d{3}-\d{4}"
'--- ★ここまで ---
For i = 2 To lastRow
If reg.Test(ws.Cells(i, 1).Value) Then
ws.Cells(i, 1).Value = reg.Replace(ws.Cells(i, 1).Value, "***-****")
End If
Next i
MsgBox "正規表現による置換が完了しました。"
End Sub
よく使う正規表現パターン
| パターン | 意味 | 例 |
|---|---|---|
\d{3}-\d{4} |
3桁-4桁の数字 | 郵便番号（123-4567） |
\d{2,4}-\d{2,4}-\d{4} |
電話番号 | 03-1234-5678 |
[A-Z]{2}\d{4} |
英字2文字+数字4桁 | コード体系（AB1234） |
\s+ |
1つ以上の空白文字 | 連続スペースの統一 |
^0+ |
先頭のゼロ | 先頭ゼロの除去 |
正規表現はこの記事の補足的な位置づけ。まずはRange.Replace（最小版）とArray配列の置換リスト（実務版）で十分な業務がほとんど。正規表現が必要になるのは、パターンが固定文字列ではなく「桁数指定」「文字種指定」の場合。
よくある落とし穴5選
1. LookAt:=xlPartで意図しない部分一致が発生する
原因: xlPart（部分一致）がデフォルト。「東京」を「大阪」に置換すると、「東京都」が「大阪都」になる。「東京支店」が「大阪支店」になる。意図しない置換が広がる。
自分も取引先コードの置換で「A01」を「B01」に変えたら、「A0100」「A012」まで巻き込まれて大変なことになった。発覚したのは提出後。30分かけて全データを再チェックした。
対策: 完全一致で置換する場合は LookAt:=xlWhole を指定する。部分一致が必要な場合は、置換リストの順番を「長い文字列→短い文字列」にして、意図しないマッチを防ぐ。
2. 数式セルを置換して数式が壊れる
原因: Range.Replaceの LookIn 引数を省略すると、デフォルトで xlFormulas が適用される。数式内の文字列も置換対象になり、=VLOOKUP("株式会社ABC",...) の「株式会社」が「(株)」に変わって数式が壊れる。
対策: LookIn:=xlValues を追加して、値のみを対象にする。
ws.Range("A2:D" & lastRow).Replace _
What:="株式会社", _
Replacement:="(株)", _
LookAt:=xlPart, _
MatchCase:=False, _
LookIn:=xlValues '← 値のみ対象（数式は置換しない）
3. 大文字小文字の区別を忘れて意図しない置換が発生する
原因: MatchCase を省略するとFalse（区別しない）がデフォルト。「ABC」を「XYZ」に置換すると、「abc」や「Abc」も置換される。コード体系が大文字小文字で区別されている場合に問題になる。
対策: 大文字小文字を区別したい場合は MatchCase:=True を明示する。
4. 置換結果が意図と違うが大量データで気づかない
原因: 500件のデータに対して一括置換を実行すると、1件ずつの確認が現実的でない。置換パターンの指定ミスや部分一致の罠に気づかないまま処理が完了する。
対策: 本番実行前にテスト用シートで事前確認する。検索ハイライト（特定の文字を含むセルを検索してハイライト）で置換対象を可視化してから実行すると安心。
5. VBAで実行した置換はCtrl+Zで元に戻せない
原因: Excelの「元に戻す」機能（Ctrl+Z）はVBAの処理に対しては動作しない。一度実行すると、置換前のデータに戻す手段がない。
対策: 実行前にファイルのバックアップを取る。または、マクロの先頭で対象シートのコピーを作成する処理を入れる。空白行の削除（空白行・空白セルを一括で削除する方法）など他の破壊的操作でも同様にバックアップが重要。
FAQ
Q1: Range.ReplaceとVBA Replace関数の違いは？
Range.Replaceはセル範囲に対して一括で置換する。ExcelのCtrl+Hと同じ動作。VBA Replace関数は文字列変数に対して置換する。1セルずつループで処理するため、If文による条件分岐と組み合わせられる。単純な一括置換ならRange.Replace。条件付き置換ならVBA Replace関数。
Q2: 全角スペースを半角スペースに一括変換できる？
できる。置換リストに Array(" ", " ") を追加するだけ。全角カタカナを半角カタカナに変換したい場合はReplace関数ではなく StrConv(文字列, vbNarrow) を使う。
Q3: 複数シートに対して一括置換できる？
できる。For EachでWorksheets全体をループし、各シートにRange.Replaceを実行すればよい。複数シートへの一括処理の基本は 複数シートに同じ処理を一括実行 を参照。
Q4: 置換前に何件ヒットするか確認したい
Range.FindとFindNextでループしてカウントする方法がある。検索ハイライト（特定の文字を含むセルを検索してハイライト）の手法を応用すれば、ヒット箇所を色付きで確認してから置換を実行できる。自分は重要な置換の前には必ずこの確認をやるようにしている。
Q5: 正規表現で置換するには参照設定が必要？
不要。CreateObject("VBScript.RegExp") で参照設定なしで使える。ただし正規表現のパターン記述にはある程度の知識が必要。この記事の補足セクションのパターン早見表を参考にするとよい。
まとめ
Range.Replaceで指定範囲の文字列を一括置換できる（Ctrl+Hと同じ動作）- 置換リスト（Array配列）で複数パターンを管理すれば、何パターンでも一括実行できる
- 条件付き置換はVBA Replace関数でセル単位で処理する
- 正規表現（VBScript.RegExp）でパターンマッチの高度な置換も可能
- VBAの置換はCtrl+Zで戻せない。実行前のバックアップが必須
関連記事
- 特定の文字を含むセルを検索してハイライト — 置換前にヒット箇所を可視化して確認する
- 複数シートに同じ処理を一括実行 — 全シートに同じ置換処理を適用する
- セルの書式を一括変更する方法 — 置換と合わせて書式も整える
次にやりたくなること
- 特定の文字を含むセルを検索してハイライト: 置換前にヒット箇所を色付きで確認してから一括置換を実行する
- 空白行・空白セルを一括で削除する方法: 置換後のデータ整理で空白行を一括削除する
もっとカスタマイズしたい場合
「置換パターンを別シートの表から読み込みたい」「置換前後のログを残したい」「正規表現で複雑なパターンを使いたい」など、業務に合わせたカスタマイズが必要な場合は、ココナラで相談できます。
相談時に以下の情報があるとスムーズです：
- Excel のバージョン / OS
- 置換対象のデータ構成（列構成、データ件数の目安）
- 置換パターンの一覧（検索文字列と置換文字列のペア）
- 条件付き置換が必要な場合はその条件
コメント