shikumika’s diary

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

Excelで特定の文字より後ろにある文字列を抽出するユーザー定義関数サンプル

Excelで少し複雑な文字列抽出をしたく、Excelのユーザー定義関数を作成してみたので備忘メモ。
簡単なものであれば、LEFT関数、MID関数、FIND関数で抽出できると思いますが、少し複雑で、大量なデータの場合は正規表現が便利です。
なお、ExcelやVBAの標準機能では正規表現が使用できませんので、次のようなユーザー定義関数を作る必要があります。

shikumika.org

ただし、上記のVBScript.RegExpの正規表現では、

  • 特定の文字より前(もしくは後ろ)にある数字の抽出
  • 特定の文字と文字の間にある数字の抽出

といったことが簡単にはできなさそうです。

前述のユーザー定義関数サンプルを少し修正して、少し複雑な文字列抽出ができるユーザー定義関数を作成してみました。

アウトプットイメージ

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

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

例えば、この場合は"正規"の後にくる”専”の40の数値を抽出
上記で3つめの文字列の場合は、"正規"がないので該当する数値なしとしたい。

括弧()で、グループ化した正規表現を使って抽出するようなユーザー定義関数としました。

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(0).submatches(グループ番号 - 1) ' ) 'グループ番号は取り出したいItemの番号。0から始まる
End Function

submatchesから抽出しています。

参考にしたコード

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

正規表現で使用できる文字およびエスケープ シーケンスの一覧

docs.microsoft.com

ユーザー定義関数の補足

ユーザー定義関数の使い方等の補足は以下参考 shikumika.org