Excel で正規表現 (RegExp) を使用してテキストを解析する

Excel でテキストを操作するときに最も時間がかかり、イライラするタスクの XNUMX つは、 解析する – 英数字の「お粥」をコンポーネントに解析し、そこから必要なフラグメントを抽出します。 例えば：

• 住所から郵便番号を抽出する (郵便番号が常に先頭にあればよいのですが、そうでない場合はどうでしょうか?)
• 銀行取引明細書の支払いの説明から請求書の番号と日付を見つける
• 取引相手のリストにある企業の雑多な説明からの TIN の抽出
• 説明などで車の番号または品番を検索します。
  

通常、このような場合、手動でテキストを XNUMX 分間退屈に選択した後、このプロセスを自動化するための考えが頭に浮かび始めます (特にデータが多い場合)。 いくつかのソリューションがあり、さまざまな程度の複雑さと効率があります。

• 　 組み込みの Excel テキスト関数 検索-カット-グルー テキスト: レブシム （左）, RIGHT （正しい）, PSTR (中), STsEPIT (連結) およびその類似体, 組み合わせる (共同テキスト), EXACT （ちょうど） この方法は、テキストに明確な論理がある場合に適しています (たとえば、インデックスは常にアドレスの先頭にあります)。 そうしないと、数式がはるかに複雑になり、場合によっては配列数式になることもあり、大きなテーブルでは速度が大幅に低下します。
• 使い方 like テキスト類似演算子 カスタム マクロ関数にラップされた Visual Basic から。 これにより、ワイルドカード文字 (*、#、? など) を使用してより柔軟な検索を実装できます。残念ながら、このツールは目的の部分文字列をテキストから抽出できません。含まれているかどうかのみを確認してください。

上記に加えて、プロのプログラマー、Web 開発者、およびその他の技術者の間で非常によく知られている別のアプローチがあります。 正規表現 (正規表現 = 正規表現 = 「正規表現」 = 「正規表現」)。 簡単に言えば、 RegExp は、特殊文字と規則を使用してテキスト内の必要な部分文字列を検索し、それらを抽出したり、他のテキストに置き換えたりする言語です。. 正規表現は非常に強力で美しいツールであり、テキストを操作する他のすべての方法よりも桁違いに優れています。 多くのプログラミング言語 (C#、PHP、Perl、JavaScript…) とテキスト エディター (Word、Notepad++…) は正規表現をサポートしています。

残念ながら、Microsoft Excel は標準で RegExp をサポートしていませんが、これは VBA で簡単に修正できます。 タブから Visual Basic Editor を開きます 開発者 （デベロッパー） またはキーボードショートカット 他の+F11. 次に、メニューから新しいモジュールを挿入します インサート – モジュール そこに次のマクロ関数のテキストをコピーします。

Public Function RegExpExtract(Text As String, Pattern As String, Optional Item As Integer = 1) As String On Error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (Text) Then Set matches = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Visual Basic Editor を閉じて Excel に戻り、新しい機能を試すことができます。 その構文は次のとおりです。

=RegExpExtract( Txt ; パターン ; 項目 )

コラボレー

• TXT – チェック対象のテキストを含むセルで、そこから必要な部分文字列を抽出したい
• パターン – 部分文字列検索のマスク (パターン)
• アイテム価格 – 抽出される部分文字列のシーケンス番号 (複数ある場合) (指定されていない場合は、最初に出現したものが表示されます)

もちろん、ここで最も興味深いのは Pattern です。RegExp の「言語内」の特殊文字のテンプレート文字列であり、正確に何をどこで見つけたいかを指定します。 開始するための最も基本的なものを次に示します。

たとえば、XNUMX 桁の郵便番号やすべて XNUMX 文字の製品コードなど、特定の数の文字を探している場合は、私たちが助けになります 数量詞 or 数量詞 検索する文字数を指定する特殊な式です。 量指定子は、その前にある文字に適用されます。

それでは、最も興味深い部分に移りましょう - 作成された関数のアプリケーションの分析と、人生からの実際の例のパターンについて学んだことです.

テキストから数字を抽出する

まず、単純なケースを分析しましょう。たとえば、価格表から無停電電源装置の電力など、英数字のお粥から最初の数字を抽出する必要があります。

正規表現の背後にあるロジックは単純です。 d は任意の数字を意味し、数量詞 + それらの数はXNUMXつ以上であるべきだと言っています。 抽出された文字を数値としてのテキストから完全な数値に「その場で」変換するには、関数の前に二重マイナスが必要です。

郵便コード

一見すると、ここではすべてが単純です。正確に XNUMX 桁が連続して検索されます。 特殊文字を使用しています d 数字と量指定子 6 {} 文字数の場合:

ただし、行のインデックスの左側に、別の大きな数字セット (電話番号、TIN、銀行口座など) が連続している場合が考えられます。その場合、レギュラー シーズンでは最初の 6 が除外されます。つまり、正しく機能しません。

これを防ぐには、正規表現の端に修飾子を追加する必要があります b 単語の終わりを意味します。 これにより、必要なフラグメント (インデックス) が別のフラグメント (電話番号) の一部ではなく、別の単語であることが Excel に明確になります。

電話

テキストで電話番号を見つける際の問題は、番号を書くためのオプションが非常に多いことです。ハイフンの有無、スペース、括弧内の地域コードの有無などです。したがって、私の意見では、最初に、いくつかのネストされた関数を使用して、ソース テキストからこれらすべての文字を削除します 代替 （代わりの）それがくっついて単一の全体になり、次にプリミティブレギュラーと 日{11} 11 桁を連続して取り出す:

ITN

TIN (わが国) は 10 桁 (法人の場合) または 12 桁 (個人の場合) になる可能性があるため、ここではもう少し複雑です。 特に欠点がなければ、通常版で満足できる可能性は十分にあります。 日{10,12}、しかし、厳密に言えば、10〜12文字のすべての数字、つまり誤って11桁を入力してしまいます。 論理 OR 演算子で接続された XNUMX つのパターンを使用する方が正しいでしょう。 | (縦棒):

クエリでは、最初に 12 ビットの数値を検索し、次に 10 ビットの数値を検索することに注意してください。 正規表現を逆に書くと、長い 12 ビット TIN であっても、最初の 10 文字だけが抽出されます。 つまり、最初の条件がトリガーされた後、それ以上の検証は実行されなくなります。

これは、オペレーターの基本的な違いです。 | 標準の Excel ロジック関数から OR （OR）、引数を並べ替えても結果は変わりません。

製品のSKU

多くの企業では、商品やサービス (商品、SAP コード、SKU など) に一意の識別子が割り当てられています。表記法に論理があれば、正規表現を使用してテキストから簡単に抽出できます。 たとえば、記事が常に XNUMX つの英大文字、ハイフン、およびそれに続く XNUMX 桁の数字で構成されていることがわかっている場合、次のようになります。

テンプレートの背後にあるロジックは単純です。 [AZ] – は、ラテン アルファベットの大文字を意味します。 次の数量詞 3 {} そのような文字が正確にXNUMXつあることが私たちにとって重要であると言います。 ハイフンのあとはXNUMX桁待ちなので最後に追加 日{3}

現金金額

前の段落と同様に、商品の説明から価格 (コスト、VAT など) を引き出すこともできます。 たとえば、金額がハイフンで示されている場合は、次のようになります。

パターン d 数量詞付き + ハイフンまでの任意の数字を検索し、 日{2} その後、ペニー (XNUMX 桁) を探します。

価格ではなく VAT を抽出する必要がある場合は、抽出する要素の序数を指定する RegExpExtract 関数の XNUMX 番目のオプション引数を使用できます。 そしてもちろん、関数を置き換えることができます 代替 （代わりの） 結果では、標準の小数点記号にハイフンを追加し、先頭にマイナスを XNUMX つ追加して、Excel が検出された VAT を通常の数値として解釈するようにします。

車のナンバー

特殊車両、トレーラー、その他のオートバイを利用しない場合、標準の車の番号は「文字 - 2 つの数字 - 3 つの文字 - 地域コード」の原則に従って解析されます。また、地域コードはXNUMX桁またはXNUMX桁で、文字としてはラテンアルファベットに似たもののみが使用されます。したがって、次の正規表現は、テキストから数値を抽出するのに役立ちます。

Time

HH:MM 形式で時刻を抽出するには、次の正規表現が適しています。

結腸フラグメントの後 [0-5]日は、わかりやすいように、00 ～ 59 の範囲の任意の数値を設定します。 括弧内のコロンの前では、論理 OR (パイプ) で区切られた XNUMX つのパターンが機能します。

• [0-1]日 – 00 ～ 19 の範囲の任意の数値
• 2 [0-3] – 20 ～ 23 の範囲の任意の数値

得られた結果に、さらに標準の Excel 関数を適用できます。 タイム （チーム）プログラムが理解でき、さらなる計算に適した時間形式に変換します。

パスワードチェック

ユーザーが作成したパスワードのリストが正しいかどうかをチェックする必要があるとします。 当社の規則によると、パスワードには英字 (小文字または大文字) と数字のみを含めることができます。 スペース、アンダースコア、その他の句読点は使用できません。

チェックは、次の単純な正規表現を使用して整理できます。

実際、このようなパターンでは、最初 (^） そして終わり （$）私たちのテキストには、角括弧で指定されたセットの文字のみがありました. パスワードの長さ (たとえば、少なくとも 6 文字) も確認する必要がある場合は、数量詞 + は、次の形式で間隔「XNUMX 以上」に置き換えることができます。 {6、}:

市区町村の住所

アドレスバーから都市を取得する必要があるとしましょう。 「g」からテキストを抽出する通常のプログラムが役立ちます。 次のコンマまで:

このパターンを詳しく見てみましょう。

上記のテキストを読んだ場合は、正規表現の一部の文字 (ピリオド、アスタリスク、ドル記号など) に特別な意味があることを既に理解しています。 これらの文字自体を探す必要がある場合は、前にバックスラッシュを付けます ( 遮蔽）。 したがって、フラグメント「g」を検索する場合。 正規表現で書く必要があります 氏 プラスを探している場合は、 + 等々

テンプレートの次の XNUMX 文字、ドットと数量詞アスタリスクは、任意の数の任意の文字、つまり任意の都市名を表します。

「g」からのテキストを探しているため、テンプレートの最後にカンマがあります。 コンマに。 しかし、テキストには複数のコンマが含まれる場合がありますよね? 都市の後だけでなく、通りや家などの後でも、私たちの要求はどれに止まるでしょうか？ 疑問符はそのためのものです。 それがなければ、正規表現は可能な限り長い文字列を引き出します:

正規表現で言えば、このようなパターンは「貪欲」です。 この状況を修正するには、クエスチョン マークが必要です。クエスチョン マークを付けると、量指定子が「けち」になります。このクエリでは、「g.」の後の最初のカウンタ コンマまでのテキストのみが取得されます。

フルパスからのファイル名

別の非常に一般的な状況は、フル パスからファイル名を抽出することです。 ここでは、次の形式の単純な正規表現が役立ちます。

ここでのトリックは、実際には検索が反対方向に発生することです。つまり、テンプレートの最後にあるため、最後から最初に $、右から最初のバックスラッシュまで、その前のすべてを探しています。 バックスラッシュは、前の例のドットのようにエスケープされます。

PS

「最後に」上記のすべては、正規表現が提供するすべての可能性のごく一部であることを明確にしたいと思います。 多くの特殊文字とその使用規則があり、このトピックについては本全体が書かれています (最初に少なくともこれをお勧めします)。 ある意味で、正規表現を書くことはほとんど芸術です。 ほとんどの場合、発明された正規表現は改善または補足され、より洗練されたものになったり、より広い範囲の入力データを処理できるようになったりします。

他の人の正規表現を分析および解析したり、自分の正規表現をデバッグしたりするには、便利なオンライン サービスがいくつかあります。 正規表現101, RegExr もっと

残念ながら、古典的な正規表現のすべての機能が VBA でサポートされているわけではなく (たとえば、逆検索や POSIX クラス)、キリル文字でも機能しますが、最初のうちは十分満足できるものがあると思います。

このトピックに不慣れで、共有したいことがある場合は、Excel で作業するときに役立つ正規表現を以下のコメントに残してください。 一気はいいけど、ブーツはXNUMX本がペア！

• SUBSTITUTE 関数によるテキストの置換とクリーンアップ
• テキスト内のラテン文字の検索とハイライト
• 最も近い類似テキストを検索します (Ivanov = Ivonov = Ivanof など)。