shikumika’s diary

日々の事務作業で手間なことを簡単にできる仕組み(自動化、方法など)、困ったことの解決方法を調べた備忘録

Excelで正規表現を使うためのユーザー定義関数サンプル

ExcelやVBAの標準機能では正規表現が使用できません。VBScript.RegExpを使えば可能とのことで、Excelのユーザー定義関数にした処理をしてみたので備忘メモ。 EXCELのワークシート関数としても使用できます。

そもそもの背景は、Power Query(パワークエリ)で正規表現を利用したかった。調べてみると、Web.Pageの機能を使ってJavaScriptの正規表現でする方法がありました。「なるほど、と思う使い方でとても参考になったのですが、私の設定方法が悪いのか、処理速度が遅かったのでVBAですることにしました。

アウトプットイメージ

今回実施したかったことは、次のような文字列の中から、指定した場所に該当する数値を抽出すること。

正規:50 (専40) 非正規:30 (専20)

例えば、この番目に位置する40の数値を抽出

Excelでのイメージは次のとおり。
Excelのユーザー定義関数の活用イメージ

セル参照、もしくは直接の入力で、一致番号に該当する場所の数値抽出ができた。

なお、ユーザー定義関数の使用時の画面は次の通り。
Excelのユーザー定義関数の使用時の画面イメージ

作成したユーザー定義関数は、次のとおり。

Function 正規表現で文字列抽出(パターン As String, 検索対象文字列 As String, Optional 一致番号 As Integer = 1)
    
    Dim Matches As Object      ' 変数を作成します。
    Set regEx = CreateObject("VBScript.RegExp") ' 正規表現を作成します。
    
    regEx.Pattern = パターン         ' パターンを設定します。
    regEx.IgnoreCase = True         ' 大文字と小文字を区別しないように設定します。
    regEx.Global = True         ' True 複数回マッチ、FALSEは1回のみ
    
    Set Matches = regEx.Execute(検索対象文字列)   ' 検索を実行します。
    
    正規表現で文字列抽出 = Matches(一致番号 - 1).Value ' 検索を実行します。Itemは0からスタートなので1引く
    
End Function

参考にしたコード

参考にしたコードは以下で、正規表現で一致した場所の指定追加などを変更。 docs.microsoft.com

Power Query(パワークエリ)での正規表現抽出よりは、事前にデータ処理が必要だが処理速度は速くなり、私の実用では十分な速度になりました。

ユーザー定義関数の補足

なお、ユーザー定義関数のEXCELからの利用方法は、fxのアイコン(関数の挿入)を押して、ユーザー定義を選択すれば利用できます。

正規表現で少し複雑な文字列抽出する場合のユーザー定義関数サンプル

shikumika.org