Excel VBA で複数のセルの文字列を効率的に置き換えることができます。文字列の置換を一括で行う方法について、基本コードから活用例までを解説します。
Replace関数の基本
Excel VBA で文字列を置き換えるには、Replace関数を使用します。
【基本構文】
Replace(文字列, 検索文字列, 置換文字列)
- 文字列: 置換を行う対象の文字列。
- 検索文字列: 検索する文字列。
- 文字列を置換: 検索文字列を置換する文字列。
基本的な使用方法
文字列の一部を置き換えます。
Sub ReplaceExample()
Dim originalText As String
Dim replacedText As String
originalText = "Hello, World!"
replacedText = Replace(originalText, "World", "VBA")
MsgBox replacedText ' 結果: "Hello, VBA!"
End Sub
セル範囲内で文字列を置き換える
指定したセル範囲で特定の文字列を置き換える例です。
Sub ReplaceInRange()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
For Each cell In ws.Range("A1:A10")
If Not IsEmpty(cell.Value) Then
cell.Value = Replace(cell.Value, "旧文字列", "新文字列")
End If
Next cell
End Sub
解説
Range("A1:A10")の範囲で文字列を置き換えます。Replace(cell.Value, "旧文字列", "新文字列")を使って、セルの値を変更します。IsEmpty関数で空セルをスキップします。
ワイルドカードを使った置き換え
ワイルドカードを含む文字列の置換は、Replace関数では直接対応できないため、Like演算子を使用して条件付きで置換を行います。
Sub ReplaceInRange()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
For Each cell In ws.Range("A1:A10")
If Not IsEmpty(cell.Value) Then
cell.Value = Replace(cell.Value, "旧文字列", "新文字列")
End If
Next cell
End Sub
ポイント
Like "*特定文字*"を使って、「特定の文字」が含まれるセルを判定します。
シート全体の置き換え
シート全体の文字列を一括で置き換えるには、以下のコードを使用します。
Sub ReplaceInEntireSheet()
Dim ws As Worksheet
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
For Each cell In ws.UsedRange
If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
cell.Value = Replace(cell.Value, "旧文字列", "新文字列")
End If
Next cell
End Sub
解説
UsedRangeを使用して、シート内のデータが入力されている範囲を対象とします。【VBA】UsedRangeプロパティ：最終行と最終列数VarType(cell.Value) = vbStringで文字列型のセルのみ置換対象となります。【VBA】VarType 関数を使ってデータ型を調べる方法
複数の文字列を一括置換する
複数の検索文字列をまとめて置換する場合は、配列を使用する方法が便利です。
Sub ReplaceMultipleStrings()
Dim ws As Worksheet
Dim cell As Range
Dim findReplacePairs As Variant
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
findReplacePairs = Array("旧文字列1", "新文字列1", "旧文字列2", "新文字列2")
For Each cell In ws.UsedRange
If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
For i = LBound(findReplacePairs) To UBound(findReplacePairs) Step 2
cell.Value = Replace(cell.Value, findReplacePairs(i), findReplacePairs(i + 1))
Next i
End If
Next cell
End Sub
解説
findReplacePairs配列に「旧文字列」と「新文字列」のペアを設定します。Step 2を使用して配列を2つずつ処理します。- シート全体を対象に、一括で複数の文字列をセルに置き換えます。
削除結果を確認できるようにする
削除結果を確認しながら処理を進めたい場合は、InputBoxやMsgBoxを利用します。
Sub ConfirmAndReplace()
Dim ws As Worksheet
Dim cell As Range
Dim oldText As String
Dim newText As String
Dim confirm As VbMsgBoxResult
Set ws = ThisWorkbook.Worksheets("Sheet1")
oldText = InputBox("置き換えたい文字列を入力してください:")
newText = InputBox("新しい文字列を入力してください:")
For Each cell In ws.UsedRange
If Not IsEmpty(cell.Value) And InStr(cell.Value, oldText) > 0 Then
confirm = MsgBox("セルの内容 '" & cell.Value & "' を '" & Replace(cell.Value, oldText, newText) & "' に置き換えますか？", vbYesNoCancel)
If confirm = vbYes Then
cell.Value = Replace(cell.Value, oldText, newText)
ElseIf confirm = vbCancel Then
Exit Sub
End If
End If
Next cell
End Sub
解説
InputBoxを使って、ユーザーが置き換えたい文字列を指定します。- 置き換えの内容を表示して、確認を取りながら置き換えを実行します。
注意点
- 空白セルの処理
空白セルをスキップする処理を追加することで、エラーを防ぐことができます。 - 数値型のセル
数値型のセルにはReplace関数が適用されないため、文字列型のセルのみを対象とする条件を追加しましょう。【VBA】数値か否か判定（チェック）する方法 - 処理速度
大量のセルを対象にする場合、処理速度が遅くなる可能性があります。範囲を限定する、あるいはScreenUpdatingで画面の反映を無効にするなどの工夫を検討してください。【VBA】Application.ScreenUpdatingプロパティの使用方法と特徴
まとめ
Excel VBAを使うことで、ワークシート上の文字列の置換を効率的に行います。以下のポイントを押さえて、実際に活用してください。
Replace関数:基本的な文字列置換に使用します。- セル範囲指定:範囲を限定することで効率的に処理。
- 配列での複数置換: 配列を使用することで、複数の文字列を一括置換。
- 確認プロンプト: ユーザーに確認を求めながら安全に処理します。