Excelで文字列を抽出するための正規表現（1つまたはすべての一致）

このチュートリアルでは、Excelで正規表現を使用して、特定のパターンに一致するサブストリングを見つけて抽出する方法を学びます。

Microsoft Excelは、セルからテキストを抽出するための多くの機能を提供します。これらの機能は、ワークシートの文字列抽出の課題のほとんどに対処できます。すべてではありませんが、ほとんどです。テキスト機能がつまずくと、定期的な表現が救助されます。待ってください…Excelには正規表現機能がありません！確かに、組み込み関数はありません。しかし、あなたがあなた自身のものを使うことを妨げるものは何もありません:)

• VBA regexp抽出関数
  • 文字列から番号を抽出します
  • すべてのマッチを取得します
  • 文字列からメールアドレスを取得します
  • 電子メールドメインを抽出します
  • 電話番号を抽出します
  • 抽出日
  • 時間を選びます
• .NET Regex Extract関数
  • 2文字の間にテキストを抽出します
  • 2つの文字列の間にテキストを抽出します
  • URLからドメイン名を取得します

Excel VBA regex関数は、弦を抽出します

Excelにカスタム正規表現抽出機能を追加するには、VBAエディターに次のコードを貼り付けます。 VBAで正規表現を有効にするために、組み込みのMicrosoft Regexpオブジェクトを使用しています。このコードは、ExcelスペシャリストのAlex Frolovによって書かれています。

public function regexpextract（文字列、文字列としてのパターン、オプションのinstance_num as integer = 0 、 optional match_case as boolean = true ） dim matches_index as integer as integer as enter goto regexpextract = 」 " Regex.Multiline = true if if true = match_case then regex.ignorecase = false else regex.ignorecase = true end if set matches = regex.execute（text） if （0 = instance_num） then redim text_matches（matches.count -1、0 ） for matedex_index = 0 text_matches（matches_index、0）= matches.item（matches_index） next matches_index regexpextract = text_matches else regexpextract = matches.item（instance_num -1） end if end if end if ext if ext if ext if ext errhandl ：regexpextract = cverr（xlerralue）エンド機能

VBAの経験がほとんどない場合、ステップバイステップのユーザーガイドが役立つことが証明される場合があります：ExcelにVBAコードを挿入する方法。

注記。機能が機能するには、ファイルをマクロ対応のワークブック（.xlsm）として保存してください。

regexpextry構文

regexpextract関数は、正規表現に一致する値に対して入力文字列を検索し、1つまたはすべての一致を抽出します。

関数には次の構文があります。

regexpextract（テキスト、パターン、[instance_num]、[match_case]）

どこ：

• テキスト（必須） - 検索するテキスト文字列。
• パターン（必須） - 一致する正規表現。式に直接供給する場合、パターンは二重引用符で囲まれている必要があります。
• instance_num （オプション） - 抽出するインスタンスを示すシリアル番号。省略した場合、見つかったすべてのマッチ（デフォルト）を返します。
• match_case （オプション） - テキストケースを一致させるか無視するかを定義します。 trueまたは省略（デフォルト）の場合、ケースに敏感なマッチングが実行されます。 falseの場合 - ケース非感受性。

この関数は、Excel 365、Excel 2021、Excel 2019、Excel 2016、Excel 2013、Excel 2010のすべてのバージョンで機能します。

regexpextractについて知っておくべき4つのこと

Excelで機能を効果的に使用するには、注意する必要があるいくつかの重要なことがあります。

1. デフォルトでは、この例に示すように、関数は見つかったすべての一致を隣接するセルに戻します。特定の発生を取得するには、 instance_num引数に対応する数値を提供します。
2. デフォルトでは、関数は症例に敏感です。ケースと感受性のマッチングの場合、 match_case引数をfalseに設定します。 VBAの制限のため、ケースに依存しないコンストラクト（？i）は機能しません。
3. 有効なパターンが見つからない場合、関数は何も返しません（空の文字列）。
4. パターンが無効である場合、#value！エラーが発生します。

ワークシートでこのカスタム関数の使用を開始する前に、それが何ができるかを理解する必要がありますよね？以下の例では、いくつかの一般的なユースケースをカバーし、動的アレイExcel（Microsoft 365およびExcel 2021）と従来のExcel（2019以降のバージョン）で動作が異なる理由を説明します。

注記。 Out Regexの例は、妖精のシンプルなデータセット用に書かれています。彼らがあなたの本当のワークシートで完璧に機能することを保証することはできません。 Regexの経験がある人は、正規表現を書くことは完璧への終わりのない道であることに同意するでしょう。ほとんどの場合、よりエレガントになるか、より広い範囲の入力データを処理できるようにする方法があります。

文字列から数値を抽出するための正規表現

「単純なものから複雑なものへ」を教えることの基本的な格言に続いて、非常に明白なケースから始めます：文字列から数を抽出します。

最初に決定するのは、取得する数値です。最初、最後、特定の発生、またはすべての数値です。

最初の番号を抽出します

これは、正規表現が得られるのと同じくらい簡単です。 \ dが0から9までの桁を意味し、1回以上を意味することを考えると、私たちの正規表現は次の形式を取ります。

パターン：\ d

instance_numを1に設定すると、目的の結果が得られます。

=RegExpExtract(A5, "\d ", 1)

ここで、A5は元の文字列です。

便利なため、事前定義されたセル（$ 2）にパターンを入力し、$サインでアドレスをロックできます。

=RegExpExtract(A5, $A$2, 1)

最後の番号を取得します

文字列に最後の数値を抽出するには、次のパターンを次に示します。

パターン： （\ d）（？！。*\ d）

人間の言語に翻訳されていると言います：他の数字が守られていない（どこにも、すぐにではない）数字を見つけます。これを表現するために、ネガティブルックヘッド（？！。*\ d）を使用しています。つまり、パターンの右側には、他の数の文字がそれ以前にあるかどうかに関係なく、他の数字（\ d）はないはずです。

=RegExpExtract(A5, "(\d )(?!.*\d)")

ヒント：

• 特定の発生を取得するには、パターンに\ dを使用し、 emplicance_numに適切なシリアル番号を使用します。
• すべての数値を抽出する式については、次の例で説明します。

すべての一致を抽出するための正規表現

例をもう少し押し上げて、1つだけでなく、文字列からすべての数値を取得したいとします。

覚えているように、抽出された一致の数は、オプションのinstance_num引数によって制御されます。デフォルトはすべて一致するので、このパラメーターを省略するだけではありません。

=RegExpExtract(A2, "\d ")

式は単一のセルに対して美しく機能しますが、動作は動的配列Excelおよび非ダイナミックバージョンでは異なります。

Excel 365およびExcel 2021

動的配列のサポートにより、通常の式は、すべての計算結果を表示するために必要な数のセルに自動的にこぼれます。 Excelに関しては、これはこぼれた範囲と呼ばれます。

Excel 2019以降

ダイナミック以前のExcelでは、上記の式は1つの一致だけを返します。複数の一致を取得するには、アレイ式にする必要があります。このために、さまざまなセルを選択し、式を入力し、 CTRLシフトを押して入力して完了します。

このアプローチの欠点は、「エクストラセル」に表示される＃n/aエラーの束です。残念ながら、それについては何もできません（iferrorもIfnaもそれを修正することはできません）。

すべての一致を1つのセルに抽出します

データの列を処理する場合、上記のアプローチは明らかに機能しません。この場合、理想的なソリューションは、単一のセルですべての一致を返すことです。それを行うには、regexpextractの結果をTextJoin関数に提供し、あなたが好きな区切り文字でそれらを分離します。

=TEXTJOIN(", ", TRUE, RegExpExtract(A5, "\d "))

注記。 TextJoin関数はMicrosoft 365、Excel 2021、Excel 2019のExcelでのみ利用可能であるため、式は古いバージョンでは機能しません。

文字列からテキストを抽出するための正規表現

英数字の文字列からテキストを抽出することは、Excelで非常に挑戦的な作業です。正規表現では、パイと同じくらい簡単になります。否定されたクラスを使用して、数字ではないすべてのものに一致させるだけです。

パターン：[^\ d]

個々のセル（流出範囲）でサブストリングを取得するには、式は次のとおりです。

=RegExpExtract(A5, "[^\d] ")

すべての一致を1つのセルに出力するには、次のようにテキストジョインで再gexpextract関数をネストします。

=TEXTJOIN("", TRUE, RegExpExtract(A5, "[^\d] "))

Regexは、文字列からメールアドレスを抽出します

さまざまな情報を含む文字列から電子メールアドレスを引き出すには、メールアドレス構造を複製する正規表現を書きます。

パターン：[\ w \。\ - ] @[a-za-z0-9 \。\ - ] \。[a-za-z] {2,24}

この正規表現を分解してください、ここに私たちが得るものがあります：

• [\ w \。\ - ]は、1つ以上の英数字、アンダースコア、ドット、ハイフンを含むユーザー名です。
• @シンボル
• [a-za-z0-9 \。\ - ]は、大文字と小文字、数字、ハイフン、ドット（サブドメインの場合）で構成されるドメイン名です。ここではアンダースコアが許可されていないため、文字、数字、またはアンダースコアに一致する\ Wの代わりに、3つの異なる文字セット（AZ AZや0-9など）が使用されます。
• \。[a-za-z] {2,24}は、トップレベルのドメインです。ドットで構成されていて、大文字と小文字が続きます。トップレベルのドメインのほとんどは3文字の長さ（.com .org、.eduなど）ですが、理論的には2〜24文字（最長の登録TLD）を含めることができます。

文字列がA5にあり、A2のパターンであると仮定すると、電子メールアドレスを抽出する式は次のとおりです。

=RegExpExtract(A5, $A$2)

電子メールからドメインを抽出するための正規表現

電子メールドメインの抽出に関しては、最初に思い浮かぶのは、キャプチャグループを使用して @文字に直接続くテキストを見つけることです。

パターン： @（[a-za-z0-9 \。\ - ] \。[a-za-z] {2,24}）

regexp関数にそれを提供します：

=RegExpExtract(A5, "@([A-Za-z0-9\.\-] \.[A-Za-z]{2,24})")

そして、あなたはこの結果を得るでしょう：

古典的な正規表現を使用すると、キャプチャグループの外側は抽出に含まれていません。 VBA Regexの動作が異なり、「@」をキャプチャする理由は誰にもわかりません。それを取り除くために、結果から最初の文字を空の文字列に置き換えて削除できます。

=REPLACE(RegExpExtract(A5, "@([az\d][az\d\-\.]*\.[az]{2,})", 1, FALSE), 1, 1, "")

電話番号を抽出するための正規表現

電話番号はさまざまな方法で書くことができます。そのため、あらゆる状況下で動作するソリューションを思い付くことは次に不可能です。それにもかかわらず、データセットで使用されるすべての形式を書き留めて、それらを一致させることができます。

この例では、これらの形式のいずれかで電話番号を抽出する正規表現を作成します。

（123）345-6789 （123）345 6789 （123）3456789 123-345-6789

123.345.6789 123 345 6789 1233456789

パターン：\（？\ d {3} [ - \。\）]*\ d {3} [ - \。 ]？\ d {4} \ b

• 最初の部分\（？\ d {3}は、ゼロまたは1つの開く括弧と続いて3桁のd {3}を一致させます。
• [ - \。 \）]*パーツとは、正方形の括弧内のキャラクターが0回以上表示されることを意味します：ハイフン、期間、空間、または閉じた括弧。
• 次に、再び3桁のd {3}に続いてハイフン、期間、または空間が続きます[ - \。 ]？ 0または1時間が表示されます。
• その後、4桁のグループがあります\ d {4}。
• 最後に、私たちが探している電話番号が大きな数の一部ではないと定義する単語の境界\ bがあります。

完全なフォーミュラはこの形をとります：

=RegExpExtract(A5, "\(?\d{3}[-\. \)]*\d{3}[-\. ]?\d{4}\b")

上記の正規表現は、123）456 7899または（123 456 7899などのいくつかの偽陽性結果を返す可能性があることに注意してください。以下のバージョンはこれらの問題を修正します。ただし、この構文は、古典的な正規表現ではなく、VBA Regexp関数でのみ機能します。

パターン：（\（\ d {3} \）| \ d {3}）[ - \。 ]？\ d {3} [ - \。 ]？\ d {4} \ b

文字列から日付を抽出するための正規表現

日付を抽出するための正規表現は、日付が文字列内に表示される形式によって異なります。例えば：

1/1/21または01/01/2021のような日付を抽出するには、regexは次のとおりです。

1桁または2桁のd {1,2}のグループを検索し、それに続いてスラッシュが続き、その後1桁または2桁の別のグループが続き、続いて4桁または2桁（\ d {4} | \ d {2}）のグループが続きます。最初に4桁の年を探していることに注意してください。それを逆に書くと、最初の2桁のみがすべての年から選ばれます。これは、最初の条件が交互またはコンストラクトが一致した後、残りの条件がチェックされないためです。

1-jan-21や01-jan-2021などの日付を取得するには、パターンは次のとおりです。

1桁または2桁のグループを検索し、その後にハイフンが続き、次に3つの大文字または小文字のグループが続き、その後にハイフンが続き、その後4桁または2桁のグループが続きます。

2つのパターンを組み合わせた後、次の正規表現を取得します。

パターン：\ b \ d {1,2} [\/ - ]（\ d {1,2} | [a-za-z] {3}）[\/ - ]（\ d {4} | \ d {2}）\ b

どこ：

• 最初の部分は1桁または2桁です：\ d {1,2}
• 2番目の部分は1桁または2桁または3文字です：（\ d {1,2} | [a-za-z] {3}）
• 3番目の部分は4桁または2桁のグループです：（\ d {4} | \ d {2}）
• デリミッターはフォワードスラッシュまたはハイフンのいずれかです：[\/ - ]
• 単語の境界\ bが両側に配置され、日付は別の単語であり、大きな文字列の一部ではないことを明確にします。

以下の画像でわかるように、日付を正常に引き出し、11/22/333などのサブストリングを除外します。ただし、誤検知の結果は引き続き返されます。私たちの場合、A9のサブストリング11-ABC-2222は、技術的に日付形式のDD-MMM-YYYYと一致するため、抽出されます。

誤検知を排除するために、[a-za-z] {3}パーツを3文字の月の略語の完全なリストに置き換えることができます。

パターン：\ b \ d {1,2} [\/ - ]（\ d {1,2} |（jan | feb | mar | mar | apr | jun | Jul | 8月| sep | 10月| dec）[\/ - ]（\ d {4} | \ d {2}）\ b

文字ケースを無視するために、カスタム関数の最後の引数をfalseに設定します。

=RegExpExtract(A5, $A$2, 1, FALSE)

そして今回は、完璧な結果が得られます。

文字列から時間を抽出するための正規表現

HH：MMまたはHH：MM：SS形式で時間を取得するには、次の式が治療を行います。

パターン：\ b（0？[0-9] | 1 [0-2]）：[0-5] \ d（：[0-5] \ d）？\ s？（am | pm）\ b | \ b（[0-9] | [0-1] \ d | 2 [0-3]）：[0-5] \ d（：[0-5] \ d）？

この正規表現を分解すると、2つの部分が分離されているのがわかります。これにより、またはロジックが可能になります。つまり、以下の式の1つに一致するサブストリングを検索します。

式1 ：\ b（0？[0-9] | 1 [0-2]）：[0-5] \ d（：[0-5] \ d）？\ s？（am | PM）\ b

AM/PMで時間を取得します。

時間は0から12までの任意の数字になります。それを取得するには、またはconstruct（[0-9] | 1 [0-2]）を使用します。

• [0-9]は、0から9までの任意の数と一致します
• 1 [0-2]は、10から12の任意の数と一致します

Minute [0-5] \ dは00から59までの任意の数です。

second （：[0-5] \ d）？ 00から59までの任意の数もあります。秒数は、時間値に含まれている場合と含まれない場合があるため、ゼロまたは1回の発生を意味します。

式2 ：\ b（[0-9] | [0-1] \ d | 2 [0-3]）：[0-5] \ d（：[0-5] \ d）？（？！:)

AM/PMなしで時間を抽出します。

時間部分は0から32までの任意の数になります。

• [0-9]は、0から9までの任意の数と一致します
• [0-1] \ dは00から19の任意の数と一致します
• 2 [0-3]は20から23の任意の数と一致します

分と2番目の部分は、上記の式1と同じです。

ネガティブルックヘッド（？！:) 20:30:80などのスキップ文字列に追加されます。

PM/AMは大文字または小文字のいずれかである可能性があるため、関数をケースに敏感にします。

=RegExpExtract(A5, $A$2, 1, FALSE)

うまくいけば、上記の例が、Excelワークシートで正規表現を使用する方法に関するいくつかのアイデアを提供しました。残念ながら、古典的な正規表現のすべての機能がVBAでサポートされているわけではありません。 VBA regexpでタスクを達成できない場合は、はるかに強力な.NET Regex関数について議論する次の部分を読むことをお勧めします。

Excelでテキストを抽出するカスタム.NETベースの正規表現関数

Excelユーザーが作成できるVBA Regexp関数とは異なり、.NET Regexは開発者の領域です。 Microsoft .NETフレームワークは、Perl 5と互換性のあるフル機能の正規表現の構文をサポートしています。この記事は、そのような関数の書き方を教えていません（私はプログラマーではなく、それを行う方法についてわずかなアイデアを持っていません:)

標準の.NET Regexエンジンによって処理された4つの強力な機能は、開発者によってすでに書かれており、Ultimate Suiteに含まれています。以下に、Excelでテキストを抽出するために特別に設計された関数の実用的な使用を示します。

ヒント。 .NET Regex Syntaxの詳細については、.NET正規表現言語を参照してください。

正規表現を使用してExcelで文字列を抽出する方法

Ultimate Suiteの最新バージョンがインストールされていると仮定すると、正規表現を使用してテキストを抽出すると、これらの2つのステップに要約されます。

1. The AbleBitsデータの[The Text Group]で、 Regexツールをクリックします。

   

2. Regexツールペインで、ソースデータを選択し、Regexパターンを入力し、抽出オプションを選択します。結果を値ではなくカスタム関数として取得するには、 [式]チェックボックスとして挿入を選択します。完了したら、 [抽出]ボタンをクリックします。

   

結果は、元のデータの右側の新しい列に表示されます。

ablebitregexextract構文

カスタム関数には次の構文があります。

ablebitregexextrage（参照、remular_expression）

どこ：

• 参照（必須） - ソース文字列を含むセルへの参照。
• remulal_expression （必須） - 一致する正規表現パターン。

重要なメモ！この機能は、Excelインストール用のUltimate Suiteを備えたマシンでのみ機能します。

使用法

あなたの学習曲線をよりスムーズにし、あなたの経験をより楽しくするために、これらのポイントに注意してください：

1. 式を作成するには、 Regexツール、またはExcelの挿入関数ダイアログを使用するか、セルの完全な関数名を入力できます。フォーミュラが挿入されたら、ネイティブフォーミュラのように管理（編集、コピー、または移動）を管理できます。
2. Regexツールペインに入力するパターンは、2 ^nd引数に移動します。また、正規表現を別のセルに保持することも可能です。この場合、2 ^nd引数のセル参照を使用するだけです。
3. 関数は、最初に見つかった一致を抽出します。
4. デフォルトでは、関数は症例に敏感です。ケースと感受性の一致の場合、（？i）パターンを使用します。
5. 一致が見つからない場合、＃n/aエラーが返されます。

2つの文字間で文字列を抽出するための正規表現

2つの文字間でテキストを取得するには、キャプチャグループまたはルックアラウンドのいずれかを使用できます。

括弧間でテキストを抽出しようとしているとしましょう。キャプチャグループが最も簡単な方法です。

パターン1 ：\ [（。*？）\]

前向きな外観とLookaheadで、結果はまったく同じです。

パターン2 ：（？

キャプチャグループ（。*？）が2つのブラケット間でテキストを怠zyな検索を実行することに注意してください - 最初から[最初]から。疑問符のないキャプチャグループ（。*）は、貪欲な検索を行い、最初から最後まですべてをキャプチャします。

A2のパターンを使用すると、式は次のとおりです。

=AblebitsRegexExtract(A5, $A$2)

すべての試合を取得する方法

すでに述べたように、ablebitsregexextract関数は1つの一致のみを抽出できます。すべての試合を取得するには、前述のVBA関数を使用できます。ただし、1つの警告があります - VBA regexpはキャプチャグループをサポートしていないため、上記のパターンは「境界」文字も返します。この場合はブラケットです。

=TEXTJOIN(" ", TRUE, RegExpExtract(A5, $A$2))

ブラケットを取り除くには、この式を使用して空の文字列（ ""）で置き換えます。

=SUBSTITUTE(SUBSTITUTE(TEXTJOIN(", ", TRUE, RegExpExtract(A5, $A$2)), "]", ""),"[","")

読みやすくするために、デリミッターにコンマを使用しています。

2つの文字列の間にテキストを抽出するための正規表現

2つの文字の間でテキストを引き出すために私たちが取り組んだアプローチは、2つの文字列間でテキストを抽出するためにも機能します。

たとえば、「テスト1」と「テスト2」の間にすべてを取得するには、次の正規表現を使用します。

パターン：テスト1（。*？）テスト2

完全な式は次のとおりです。

=AblebitsRegexExtract(A5, "test 1(.*?)test 2")

REGEXは、URLからドメインを抽出します

正規表現があっても、URLからドメイン名を抽出することは、些細なタスクではありません。トリックを行う重要な要素は、非キャプチャグループです。究極の目標に応じて、以下の正規表現のいずれかを選択します。

サブドメインを含む完全なドメイン名を取得するには

パターン：（？：https？\：|^| \ s）\/\/（（？：[a-za-z \ d \ - \。] {2,255} \。）？[a-za-z \ d \ - ] {1,63} \。

サブドメインのない第2レベルのドメインを取得します

パターン：（？：https？\：|^| \ s）\/\/（？：[a-za-z \ d \ - \。] {2,255} \。）？（[a-za-z \ - ] {1,63} \。

それでは、これらの正規表現がサンプルURLとして「https://www.mobile.ablebits.com」の例でどのように機能するかを見てみましょう。

• （？：https？\：|^| \ s） - 非キャプチャグループ。それは一致しますが、以下のいずれかが先行するサブストリングをキャプチャしません：https、http、string（^）、Whitespace文字（\ s）の開始。最後の2つの項目は、「//google.com」のようなプロトコル関連URLを処理するために含まれています。
• \/ \/ - 2つのフォワードスラッシュ（それぞれには、前方スラッシュの特別な意味を逃れ、文字通り解釈するためのバックスラッシュが先行します）。
• （？：[a-za-z \ d \ - \。] {2,255} \。）？ - 非キャプチャグループは、第3レベル、第4レベルなどを特定します。ドメイン（サンプルURLのモバイル）。最初のパターンでは、より大きなキャプチャグループ内に配置され、そのようなすべてのサブドメインを抽出に含めることができます。サブドメインは2〜255文字の長さである可能性があるため、{2,255}の量子です。
• （[a-za-z \ d \ - ] {1,63} \。[a-za-z] {2,24}） - グループをキャプチャして、第2レベルのドメイン（有能）とトップレベルドメイン（ com ）を抽出します。セカンドレベルのドメインの最大長は63文字です。現在存在する最長のトップレベルのドメインには、24文字が含まれています。

A2にどの正規表現が入力されるかによって、以下の式は異なる結果を生み出します。

=AblebitsRegexExtract(A5, $A$2)

すべてのサブドメインを使用してフルドメイン名を抽出するための正規表現：

サブドメインなしで第2レベルのドメインを抽出するための正規表現：

これは、正規表現を使用してExcelでテキストの部分を抽出する方法です。読んでくれてありがとう、来週私たちのブログでお会いできることを楽しみにしています！

利用可能なダウンロード

Excel Regex Extractの例（.xlsmファイル）究極のスイートトライアルバージョン（.exeファイル）

以上がExcelで文字列を抽出するための正規表現（1つまたはすべての一致）の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。