【Excel】VBAで正規表現(RegExp)を使って文字列を検索・置換する方法

VBAにおける正規表現の基本

Excel VBAで正規表現(Regular Expression)を利用するには、まず「VBScript.RegExp」オブジェクトを生成する必要があります。

このオブジェクトは、文字列の中から特定のパターンに合致する部分を検索したり、合致した部分を別の文字列に置き換えたりする強力な機能を提供します。

正規表現は、電話番号、メールアドレス、郵便番号など、決まった形式を持つ文字列の検証や抽出に特に役立ちます。VBAの標準機能だけでは難しい、柔軟な文字列操作が可能になります。

RegExpオブジェクトの生成と設定方法

正規表現を使った検索・置換を行うには、まずRegExpオブジェクトを作成し、必要なプロパティを設定します。

主なプロパティには、検索パターンを指定するPattern、大文字・小文字を区別するかどうかを指定するIgnoreCase、文字列全体がパターンに一致するかどうかを指定するGlobalなどがあります。

これらの設定を適切に行うことで、意図した通りの検索・置換処理を実行できます。

RegExpオブジェクトの生成手順

1. VBScript.RegExpオブジェクトの宣言と生成
   まず、RegExpオブジェクトを格納するための変数を宣言し、CreateObject関数を使ってオブジェクトを生成します。
2. Patternプロパティの設定
   検索したい文字列のパターンを正規表現で記述し、Patternプロパティに代入します。
3. IgnoreCaseプロパティの設定
   大文字・小文字を区別せずに検索したい場合は True を、区別したい場合は False を設定します。デフォルトは False です。
4. Globalプロパティの設定
   文字列内でパターンに一致するすべての箇所を検索・置換したい場合は True を、最初に見つかった箇所のみを対象とする場合は False を設定します。デフォルトは False です。
5. MultiLineプロパティの設定
   複数行の文字列を対象とし、各行の先頭や末尾をパターンに一致させたい場合は True を設定します。デフォルトは False です。

正規表現パターン(Pattern)の例

正規表現パターンは、検索したい文字列の形式を記述する特殊な文字列です。以下にいくつかの例を示します。

• 数字の検索: “\d”
• アルファベットの検索: “[a-zA-Z]”
• 特定の文字列(例: “Excel”)の検索: “Excel”
• メールアドレス形式の検索: “^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$”
• 電話番号形式(例: XXX-XXXX-XXXX)の検索: “^\d{3}-\d{4}-\d{4}$”

※正規表現内でバックスラッシュ(\)を使う場合は、VBAコード内ではさらにバックスラッシュでエスケープする必要があるため、”\\d” のようになります。

Matchメソッドを使った文字列の検索

RegExpオブジェクトのMatchメソッドは、指定した文字列内に正規表現パターンと一致する部分があるかどうかを検索します。

Matchメソッドは、一致した最初のMatchオブジェクトを返します。一致する部分が見つからない場合は Nothing を返します。

一致した部分の詳細情報(開始位置、長さ、一致した文字列など)を取得するのに役立ちます。

Matchメソッドの検索手順

1. RegExpオブジェクトの初期化
   上記「RegExpオブジェクトの生成と設定方法」の手順に従い、RegExpオブジェクトを生成し、Patternプロパティなどを設定します。
2. Matchメソッドの実行
   検索対象の文字列に対してMatchメソッドを実行します。引数には検索対象の文字列を指定します。
3. 結果の判定
   Matchメソッドの戻り値がNothingでないかを確認します。Nothingでなければ、パターンに一致する文字列が見つかったことになります。
4. 一致した情報の取得
   一致が見つかった場合、返されたMatchオブジェクトのFirstIndex、Length、Valueプロパティなどを使って、一致した文字列の位置や内容を取得します。

Matchメソッドのサンプルコード

以下のコードは、指定したセル範囲からメールアドレス形式の文字列を検索する例です。

“`vba
Sub SearchEmailAddress()
Dim regEx As Object
Dim matches As Object
Dim match As Object
Dim targetRange As Range
Dim cell As Range
Dim emailPattern As String

‘ 検索対象のセル範囲を設定
Set targetRange = ThisWorkbook.Sheets(“Sheet1”).Range(“A1:A10”)

‘ 正規表現オブジェクトを作成
Set regEx = CreateObject(“VBScript.RegExp”)

‘ メールアドレスのパターンを設定
emailPattern = “^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$”
regEx.Pattern = emailPattern
regEx.IgnoreCase = True ‘ 大文字小文字を区別しない
regEx.Global = False ‘ 最初に見つかったものだけを対象

‘ 各セルをループして検索
For Each cell In targetRange
If cell.Value <> “” Then
Set matches = regEx.Execute(cell.Value) ‘ Executeメソッドで検索
If matches.Count > 0 Then
Set match = matches(0) ‘ 最初の一致を取得
MsgBox “セル ” & cell.Address & ” にメールアドレスが見つかりました: ” & match.Value
End If
End If
Next cell

Set regEx = Nothing
End Sub
“`

※注意:上記のコードでは、MatchメソッドではなくExecuteメソッドを使用しています。Executeメソッドは、パターンに一致するすべてのMatchオブジェクトのコレクションを返します。Matchメソッドは最初の一致のみを返します。

Replaceメソッドを使った文字列の置換

RegExpオブジェクトのReplaceメソッドは、指定した文字列内で正規表現パターンに一致する部分を、別の文字列に置き換えます。

GlobalプロパティがTrueの場合、一致するすべての箇所が置換されます。Falseの場合は、最初に見つかった箇所のみが置換されます。

このメソッドは、不要な文字の削除、表記ゆれの統一、書式の変更などに非常に強力です。

Replaceメソッドの置換手順

1. RegExpオブジェクトの初期化
   RegExpオブジェクトを生成し、Patternプロパティ、Globalプロパティなどを設定します。GlobalをTrueにすると、すべて置換されます。
2. Replaceメソッドの実行
   置換対象の文字列に対してReplaceメソッドを実行します。第一引数に置換対象の文字列、第二引数に置換後の文字列を指定します。
3. 結果の取得
   Replaceメソッドは、置換後の新しい文字列を返します。元の文字列は変更されません。

Replaceメソッドのサンプルコード

以下のコードは、指定したセル範囲の文字列から、半角スペースをすべて削除する例です。

“`vba
Sub RemoveSpaces()
Dim regEx As Object
Dim targetRange As Range
Dim cell As Range

‘ 処理対象のセル範囲を設定
Set targetRange = ThisWorkbook.Sheets(“Sheet1”).Range(“B1:B10”)

‘ 正規表現オブジェクトを作成
Set regEx = CreateObject(“VBScript.RegExp”)

‘ 半角スペースのパターンを設定
regEx.Pattern = ” ” ‘ 半角スペース1つ
regEx.Global = True ‘ すべての半角スペースを対象

‘ 各セルをループして置換
For Each cell In targetRange
If cell.Value <> “” Then
cell.Value = regEx.Replace(cell.Value, “”) ‘ 空文字で置換(削除)
End If
Next cell

MsgBox “半角スペースを削除しました。”
Set regEx = Nothing
End Sub
“`

別の例として、電話番号のハイフンを削除するコードです。

“`vba
Sub RemoveHyphensFromPhoneNumbers()
Dim regEx As Object
Dim targetRange As Range
Dim cell As Range

Set targetRange = ThisWorkbook.Sheets(“Sheet1”).Range(“C1:C10”)

Set regEx = CreateObject(“VBScript.RegExp”)
regEx.Pattern = “-” ‘ ハイフン
regEx.Global = True ‘ すべてのハイフンを対象

For Each cell In targetRange
If cell.Value <> “” Then
cell.Value = regEx.Replace(cell.Value, “”) ‘ ハイフンを削除
End If
Next cell

MsgBox “電話番号からハイフンを削除しました。”
Set regEx = Nothing
End Sub
“`

正規表現の高度な使い方

正規表現は、単なる文字列の検索・置換だけでなく、より複雑な処理にも応用できます。

例えば、パターンに一致した部分をグループ化して、その一部だけを置換したり、置換文字列内でグループを参照したりすることも可能です。

また、MatchCollectionオブジェクトを利用することで、一致したすべての箇所を順番に処理することもできます。

グループ化と置換文字列での参照

正規表現では、括弧(())を使ってパターンの一部をグループ化できます。置換文字列では、$1、$2のように指定することで、各グループに一致した文字列を参照できます。

例えば、「YYYY-MM-DD」形式の日付を「MM/DD/YYYY」形式に変換する場合などが考えられます。

“`vba
Sub ConvertDateFormat()
Dim regEx As Object
Dim inputText As String
Dim outputText As String

inputText = “日付は2023-10-27です。”

Set regEx = CreateObject(“VBScript.RegExp”)
‘ (\d{4})-(\d{2})-(\d{2}) のようにグループ化
regEx.Pattern = “(\d{4})-(\d{2})-(\d{2})”
regEx.Global = True

‘ 置換文字列でグループを参照 ($1:年, $2:月, $3:日)
outputText = regEx.Replace(inputText, “$2/$3/$1”)

MsgBox “変換後: ” & outputText
‘ 出力: 日付は10/27/2023です。

Set regEx = Nothing
End Sub
“`

MatchCollectionを使った複数一致の処理

Executeメソッドは、パターンに一致するすべてのMatchオブジェクトのコレクション(MatchCollection)を返します。このコレクションをループ処理することで、すべての一致箇所に対して個別の処理を行うことができます。

例えば、特定の単語が文章中に何回出現するかを数えたり、各出現箇所に印をつけたりする場合に利用できます。

“`vba
Sub CountAndMarkOccurrences()
Dim regEx As Object
Dim matches As Object
Dim match As Object
Dim targetText As String
Dim searchWord As String
Dim count As Integer

targetText = “これはExcel VBAのサンプルテキストです。Excelは便利です。”
searchWord = “Excel”
count = 0

Set regEx = CreateObject(“VBScript.RegExp”)
regEx.Pattern = searchWord
regEx.IgnoreCase = True ‘ 大文字小文字を区別しない
regEx.Global = True ‘ すべての一致を対象

Set matches = regEx.Execute(targetText)

‘ 一致した数をカウント
count = matches.Count
MsgBox “‘” & searchWord & “‘ は ” & count & “回出現しました。”

‘ 各一致箇所に印をつける(例: []で囲む)
Dim resultText As String
resultText = targetText
Dim offset As Integer
offset = 0

For Each match In matches
‘ 元の文字列のインデックスを考慮して置換
resultText = Left(resultText, match.FirstIndex + offset) & “[” & match.Value & “]” & Mid(resultText, match.FirstIndex + match.Length + offset)
offset = offset + 2 ‘ 追加した文字数分オフセットを増やす
Next match

MsgBox “印をつけた結果: ” & resultText
‘ 出力: これはExcel VBAのサンプルテキストです。 [Excel]は便利です。

Set regEx = Nothing
End Sub
“`

正規表現を使う上での注意点と制限事項

正規表現は非常に強力ですが、いくつか注意すべき点や制限事項があります。

複雑な正規表現は、コードの可読性を低下させたり、意図しない結果を招いたりする可能性があります。また、パフォーマンスに影響を与える場合もあります。

これらの点を理解し、適切に利用することが重要です。

複雑な正規表現の可読性とデバッグ

正規表現パターンが長くなったり、特殊文字が多くなったりすると、その意味を理解するのが難しくなります。

デバッグが困難になるため、パターンはできるだけシンプルに保つか、コメントで意味を明記することをお勧めします。

また、オンラインの正規表現テスターなどを活用して、パターンが意図通りに動作するかを確認しながら開発を進めると良いでしょう。

パフォーマンスへの影響

特に、非常に大きなデータセットに対して複雑な正規表現を何度も実行する場合、処理に時間がかかることがあります。

パフォーマンスが問題になる場合は、正規表現を使わない代替手段(例: InStr関数、Replace関数、Split関数など)を検討したり、正規表現のパターンを見直したりすることが有効です。

GlobalオプションをFalseにして、必要最低限の検索・置換に留めることもパフォーマンス改善につながります。

VBAでの正規表現オブジェクトの参照設定

CreateObject(“VBScript.RegExp”) を使う方法は、参照設定が不要で手軽ですが、より高度な機能や IntelliSense(コード補完)を利用したい場合は、Microsoft VBScript Regular Expressions ライブラリへの参照設定を行う方法もあります。

参照設定を行うには、VBAエディタで「ツール」→「参照設定」を開き、「Microsoft VBScript Regular Expressions 5.5」にチェックを入れます。

参照設定後のコード例:

“`vba
Sub SearchWithReference()
Dim regEx As VBScript_RegExp_55.RegExp ‘ 型宣言をVBScript_RegExp_55.RegExp に変更
Dim matches As VBScript_RegExp_55.MatchCollection
Dim match As VBScript_RegExp_55.Match
Dim targetText As String

targetText = “これはExcel VBAのサンプルテキストです。”

Set regEx = New VBScript_RegExp_55.RegExp ‘ New キーワードで生成
regEx.Pattern = “Excel”
regEx.IgnoreCase = True
regEx.Global = True

Set matches = regEx.Execute(targetText)

MsgBox “‘Excel’ が ” & matches.Count & “回見つかりました。”

Set regEx = Nothing
End Sub
“`

この方法では、コード補完が効くため、開発効率が向上する場合があります。

まとめ

この記事では、Excel VBAで正規表現(RegExp)オブジェクトを使用して文字列を検索・置換する方法を解説しました。

RegExpオブジェクトを生成し、PatternやGlobalなどのプロパティを設定することで、複雑な条件での検索や一括置換が可能になります。

Matchメソッドで一致箇所を検索し、Replaceメソッドで置換する基本的な使い方から、グループ化による応用までを学びました。

正規表現を使いこなすことで、Excel VBAでのデータ処理能力を大幅に向上させることができます。ぜひ、実際の業務で活用してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】