Microsoft Excelでインセルとループの両方で正規表現(リージェックス)を使用する方法

regex excel vba


Excelで正規表現を使用して、データ操作にExcelの強力なグリッドのような設定を活用するにはどうすればいいのでしょうか?

  • 文字列内のマッチしたパターンまたは置換された値を返すインセル関数です。
  • サブは、データの列をループして、隣接するセルとの一致を抽出します。
  • どのような設定が必要ですか?
  • エクセルの正規表現の特殊文字とは?

excelは同様の操作に LeftMidRightInstr タイプのコマンドを使用できるため、正規表現は多くの状況(正規表現を使用するかどうか)には理想的ではないことを理解しています。





Answer 1 Portland Runner


正規表現はパターンマッチングに使用されます。

Excelで使用するには、以下の手順に従ってください。

手順1:VBA参照を「Microsoft VBScript正規表現5.5」に追加する

  • [開発者]タブを選択します(このタブがありません。どうすればよいですか?
  • コード」リボンセクションから「Visual Basic」アイコンを選択します。
  • Microsoft Visual Basic for Applications」ウィンドウで、トップメニューから「ツール」を選択します。
  • リファレンス」を選択します。
  • Microsoft VBScript 正規表現 5.5」の横にあるチェックボックスをチェックして、ワークブックに含めます。
  • OK "をクリックしてください

ステップ2:パターンを定義する

基本的な定義:

- 範囲。

  • たとえば a-z はaからzまでの小文字に一致します
  • たとえば、 0-5 は0から5までの任意の数値に一致します

[] これらの括弧内のオブジェクトの1つと正確に一致します。

  • たとえば、 [a] は文字aに一致します
  • たとえば、 [abc] は、a、b、cのいずれかの1文字に一致します。
  • たとえば、 [a-z] はアルファベットの小文字1文字と一致します。

() リターンの目的で異なる一致をグループ化します。以下の例を参照してください。

{} その前に定義されたパターンの繰り返しコピーの乗数。

  • たとえば、 [a]{2} は 2つの連続した小文字 aa 一致します:aa
  • [a]{1,3} は、少なくとも1つから3つまでの小文字 aaaaaa に一致します

+ その前に定義されたパターンの少なくとも1つ以上に一致します。

  • たとえば a+ は、連続するaの aaaaaa などに一致します。

? その前に定義されたゼロまたは1つのパターンに一致します。

  • 例:パターンは存在してもしなくてもよいが、一度だけしか一致させることができない。
  • [a-z]? 空の文字列または単一の小文字に一致します。

* その前に定義されたパターンの0個以上に一致します。-たとえば、存在する場合と存在しない場合があるパターンのワイルドカード。-たとえば、 [a-z]* 空の文字列または小文字の文字列に一致します。

. 改行以外の任意の文字に一致します \n

  • たとえば a. aで始まり、 \n 以外の何かで終わる2文字の文字列に一致します

| OR演算子

  • たとえば a|b は、 a または b のいずれかが一致できることを意味します。
  • たとえば、 red|white|orange は、いずれかの色と正確に一致します。

^ NOT演算子

  • たとえば、 [^0-9] 文字に数字を含めることはできません
  • たとえば、 [^aA] 文字を小文字の a または大文字の A にすることはできません

\ 後に 続く特殊文字をエスケープします(上記の動作を上書きします)

  • 例: \.\\\(\?\$\^

アンカーパターン:

^ 文字列の先頭で一致する必要があります

  • ^a 最初の文字は小文字の a である必要があります
  • ^[0-9] 最初の文字は数字でなければなりません。

$ 一致は文字列の最後で発生する必要があります

  • a$ 最後の文字は小文字の a である必要があります

優先順位表:

Order  Name                Representation
1      Parentheses         ( )
2      Multipliers         ? + * {m,n} {m, n}?
3      Sequence & Anchors  abc ^ $
4      Alternation         |

定義済みの文字の略語:

abr    same as       meaning
\d     [0-9]         Any single digit
\D     [^0-9]        Any single character that's not a digit
\w     [a-zA-Z0-9_]  Any word character
\W     [^a-zA-Z0-9_] Any non-word character
\s     [ \r\t\n\f]   Any space character
\S     [^ \r\t\n\f]  Any non-space character
\n     [\n]          New line

例1マクロとして実行

次のマクロ例は、セル A1 の値を調べて、最初の1文字または2文字が数字であるかどうかを確認します。存在する場合は削除され、残りの文字列が表示されます。一致しない場合は、一致するものが見つからないことを示すボックスが表示されます。セル A1 の値 12abcabc を返し、値 1abcabc を返します。数字が文字列の先頭になかったため、 abc123 の値は「一致しない」を返します。

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1")

    If strPattern <> "" Then
        strInput = Myrange.Value

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.Test(strInput) Then
            MsgBox (regEx.Replace(strInput, strReplace))
        Else
            MsgBox ("Not matched")
        End If
    End If
End Sub

例2セル内関数として実行

この例は例 1 と同じですが、インセル関数として実行されるように設定されています。使用するには、コードをこのように変更します。

Function simpleCellRegex(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String


    strPattern = "^[0-9]{1,3}"

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
        Else
            simpleCellRegex = "Not matched"
        End If
    End If
End Function

文字列( "12abc")をセル A1 に配置します。セル B1 にこの数式 =simpleCellRegex(A1) を入力すると、結果は「abc」になります。

results image


例3範囲のループ

この例は例1と同じですが、セルの範囲をループします。

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A5")

    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.Test(strInput) Then
                MsgBox (regEx.Replace(strInput, strReplace))
            Else
                MsgBox ("Not matched")
            End If
        End If
    Next
End Sub

例4:異なるパターンを分割する

この例では、範囲( A1A2 、および A3 )をループして、3桁で始まり、その後に1つの英字と4桁の数字が続く文字列を探します。出力は、 () を使用してパターンマッチを隣接するセルに分割します。 $1 は、 () の最初のセット内で一致した最初のパターンを表します。

Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A3")

    For Each C In Myrange
        strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"

        If strPattern <> "" Then
            strInput = C.Value

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.test(strInput) Then
                C.Offset(0, 1) = regEx.Replace(strInput, "$1")
                C.Offset(0, 2) = regEx.Replace(strInput, "$2")
                C.Offset(0, 3) = regEx.Replace(strInput, "$3")
            Else
                C.Offset(0, 1) = "(Not matched)"
            End If
        End If
    Next
End Sub

Results:

results image


追加のパターンの例

String   Regex Pattern                  Explanation
a1aaa    [a-zA-Z][0-9][a-zA-Z]{3}       Single alpha, single digit, three alpha characters
a1aaa    [a-zA-Z]?[0-9][a-zA-Z]{3}      May or may not have preceding alpha character
a1aaa    [a-zA-Z][0-9][a-zA-Z]{0,3}     Single alpha, single digit, 0 to 3 alpha characters
a1aaa    [a-zA-Z][0-9][a-zA-Z]*         Single alpha, single digit, followed by any number of alpha characters

</i8>    \<\/[a-zA-Z][0-9]\>            Exact non-word character except any single alpha followed by any single digit



Answer 2 Patrick Böker


Excelの数式で正規表現を直接使用するには、以下のUDF(ユーザー定義関数)が役立ちます。これは多かれ少なかれ、エクセルの関数として正規表現機能を直接公開しています。

どのように動作するか

2~3個のパラメータが必要です。

  1. 正規表現を使用するためのテキスト。
  2. 正規表現です。
  3. 結果の外観を指定するフォーマット文字列。 $0$1$2 などを含めることができます。 $0 は完全一致、 $1 は正規表現のそれぞれの一致グループに対応します。デフォルトは $0 です。

いくつかの例

メールアドレスを抽出します。

=regex("Peter Gordon: [email protected], 47", "\[email protected]\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\[email protected]\w+\.\w+", "$0")

結果: [email protected]

複数の部分文字列を抽出します。

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

結果: E-Mail: [email protected], Name: Peter Gordon

1つのセル内で結合された文字列を複数のセル内でその構成要素に分解すること。

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)

結果: Peter Gordon [email protected] ...

使用方法

このUDFを使用するには、次の手順を実行します(おおよそこのMicrosoftページに基づいています。そこには、いくつかの優れた追加情報があります)。

  1. マクロが有効なファイル( '.xlsm')のExcelで、 ALT+F11 を押して、Microsoft Visual Basic for Applications Editor を開きます
  2. VBA参照を正規表現ライブラリに追加します(Portland Runners ++ answerから恥知らずにコピーされます):
    1. [ ツール] -> [ 参照]をクリックします(ドイツのスクリーンショットはご遠慮ください)。Tools -> References
    2. リストからMicrosoft VBScript正規表現5.5を見つけ、その横にあるチェックボックスをオンにします。
    3. OKをクリックします。
  3. [ モジュールの挿入]をクリックします。あなたのモジュールに別の名前のメイクを与える場合は必ずモジュールがないではない(例えばモジュールの命名以下のUDFと同じ名前持ち Regex 、関数 regex 原因#NAME!エラー)。

    Second icon in the icon row -> Module

  4. 真ん中の大きなテキストウィンドウに次のように挿入します。

    Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant
        Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
        Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
        Dim replaceNumber As Integer
    
        With inputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = matchPattern
        End With
        With outputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "\$(\d+)"
        End With
        With outReplaceRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
        End With
    
        Set inputMatches = inputRegexObj.Execute(strInput)
        If inputMatches.Count = 0 Then
            regex = False
        Else
            Set replaceMatches = outputRegexObj.Execute(outputPattern)
            For Each replaceMatch In replaceMatches
                replaceNumber = replaceMatch.SubMatches(0)
                outReplaceRegexObj.Pattern = "\$" & replaceNumber
    
                If replaceNumber = 0 Then
                    outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
                Else
                    If replaceNumber > inputMatches(0).SubMatches.Count Then
                        'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."
                        regex = CVErr(xlErrValue)
                        Exit Function
                    Else
                        outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                    End If
                End If
            Next
            regex = outputPattern
        End If
    End Function
  5. Microsoft Visual Basic for Applications Editorウィンドウを保存して閉じます。




Answer 3 SAm


急いでいる人のためのpatszim答えを拡張します。

  1. エクセルのワークブックを開きます。
  2. Alt+F11をクリックしてVBAMacrosウィンドウを開きます。
  3. 下の正規表現への参照を追加ツール、その後の参照
    ![Excel VBA Form add references
  4. Microsoft VBScript正規表現5.5を選択する
    ![Excel VBA add regex reference
  5. 新しいモジュールを挿入します(コードはモジュール内に存在する必要があり、そうでないと動作しません)。
    ![Excel VBA insert code module
  6. 新しく挿入されたモジュールで
    ![Excel VBA insert code into module
  7. 以下のコードを追加します。

    Function RegxFunc(strInput As String, regexPattern As String) As String
        Dim regEx As New RegExp
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .pattern = regexPattern
        End With
    
        If regEx.Test(strInput) Then
            Set matches = regEx.Execute(strInput)
            RegxFunc = matches(0).Value
        Else
            RegxFunc = "not matched"
        End If
    End Function
  8. ![Excel regex function in-cell usage正規表現パターンがセルの1つに配置され、絶対参照が使用されます。関数は、作成されたワークブックに関連付けられます。
    異なるワークブックで使用する必要がある場合は、関数をPersonal.XLSBに保存します。




Answer 4 Vikas Gautam


これが私の試みです。

Function RegParse(ByVal pattern As String, ByVal html As String)
    Dim regex   As RegExp
    Set regex = New RegExp

    With regex
        .IgnoreCase = True  'ignoring cases while regex engine performs the search.
        .pattern = pattern  'declaring regex pattern.
        .Global = False     'restricting regex to find only first match.

        If .Test(html) Then         'Testing if the pattern matches or not
            mStr = .Execute(html)(0)        '.Execute(html)(0) will provide the String which matches with Regex
            RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1.
        Else
            RegParse = "#N/A"
        End If

    End With
End Function



Answer 5 DeezCashews


これをセル関数( SUMVLOOKUP など)として使用する必要があり、次の操作が簡単であることがわかりました。

  1. マクロが有効なExcelファイル(xlsmとして保存)になっていることを確認してください。
  2. オープンな開発者ツールAlt+F11
  3. 他の回答と同様にMicrosoft VBScript正規表現5.5を追加します
  4. 以下の関数をワークブック内またはモジュール内に作成します。

    Function REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant
        Dim regex As New VBScript_RegExp_55.RegExp
        Dim strInput As String
    
        strInput = myRange.Value
    
        With regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = matchPattern
        End With
    
        REGPLACE = regex.Replace(strInput, outputPattern)
    
    End Function
  5. 次に、セルで =REGPLACE(B1, "(\w) (\d+)", "$1$2") (例: "A 243"から "A243")