正則提取在Excel中提取字符串（一個或所有匹配）

在本教程中，您將學習如何在Excel中使用正則表達式來查找和提取與給定模式相匹配的子字符串。

Microsoft Excel提供了許多功能，可以從單元格中提取文本。這些功能可以應對工作表中的大多數字符串提取挑戰。大多數，但不是全部。當文本功能偶然發現時，正則表達式救援。等等…Excel沒有正則功能！是的，沒有內置功能。但是沒有什麼可以阻止您使用自己的:)

• VBA REGEXP提取功能
  • 從字符串提取號碼
  • 獲取所有比賽
  • 從字符串獲取電子郵件地址
  • 提取電子郵件域
  • 提取電話號碼
  • 提取日期
  • 選擇時間
• .NET REGEX提取功能
  • 在兩個字符之間提取文字
  • 在兩個字符串之間提取文字
  • 從URL獲取域名

Excel VBA正則函數提取字符串

要在Excel中添加自定義的Regex提取功能，請在VBA編輯器中粘貼以下代碼。為了在VBA中啟用正則表達式，我們正在使用內置的Microsoft Regexp對象。該代碼由我們的Excel專家Alex Frolov編寫。

public函數reggexpextract （文本為字符串，字符串，可選instance_num as integer = 0 ，可選match_case as boolean = true ） ​​​REGEX.MULTILINE = true如果true = Match_case the Regex.ignorecase = else else regex.ignorecase = true End如果設置匹配= regex.execute = regex.execute（text）如果0 ，則if （0 = instance_num） ，然後redim text_matches（ artees.count.count.count.count.count -1，0，0，0） text_matches（matches_index，0）= matches.item（matches_index） next matches_index regexpextract = text_matches else reggexpextract = matches.item（instance_num -1）結束如果end Inf end If Exit functionl： regexpextrack errrandl：regexpextrack errryhandl：regexpextract = cverrrv = cverrv = cverrv = cverrvalvalvalue end end end end end end end

如果您對VBA的經驗很少，則分步用戶指南可能會有所幫助：如何在Excel中插入VBA代碼。

筆記。對於工作功能，請確保將您的文件保存為宏支持的工作簿（.XLSM）。

Regexpextract語法

Regexpextract函數搜索輸入字符串以匹配正則表達式並提取一個或所有匹配的值。

該函數具有以下語法：

regexpextract（文本，模式，[instance_num]，[match_case]）

在哪裡：

• 文本（必需） - 要搜索的文本字符串。
• 模式（必需） - 匹配的正則表達式。當直接以公式提供時，應以雙引號標記封閉該圖案。
• instance_num （可選） - 指示要提取的實例的序列號。如果省略，則返回所有找到的匹配項（默認）。
• match_case （可選） - 定義是匹配還是忽略文本案例。如果true或省略（默認），則執行對案例敏感的匹配；如果是錯誤的 - 不敏感的。

該功能在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及以上版本）的行為可能有所不同。

筆記。罰款示例是為童話簡單數據集編寫的。我們不能保證它們在您的真實工作表中會完美工作。那些擁有正則經驗的人會同意，編寫正則表達方式是一條永無止境的完美之路 - 幾乎總是有一種方法使其更優雅或能夠處理更廣泛的輸入數據。

從字符串中提取數字的正則

遵循“從簡單到復雜”的基本教學最大值，我們將從一個非常簡單的情況開始：從字符串中提取數字。

您要決定的第一件事是要檢索哪個數字：首先，最後，特定的發生或所有數字。

提取第一個數字

這很簡單，就像發行正則可以得到的那樣。鑑於\ d表示從0到9的任何數字，而指的是一次或多次，我們的正則表達式採用此形式：

模式：\ d

將instance_num設置為1，您將獲得所需的結果：

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

其中A5是原始字符串。

為了方便起見，您可以將圖案輸入預定義的單元格（$ a $ 2），並用$標誌鎖定其地址：

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

獲取最後一個數字

要在字符串中提取最後一個數字，以下是要使用的模式：

模式： （\ d）（？！。*\ d）

它被翻譯成人類語言，說：找到一個沒有其他數字的數字（不僅在任何地方）。為了表達這一點，我們使用了負面的lookahead（？！。*\ d），這意味著在模式的右邊，無論其前面有多少其他字符，都不應該有其他數字（\ d）。

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

尖端：

• 要獲得特定的發生，請使用\ d進行模式，並為實例_num使用適當的序列號。
• 在下一個示例中討論了提取所有數字的公式。

提取所有匹配的正則

進一步推動我們的示例，假設您想從字符串中獲取所有數字，而不僅僅是一個。

您可能還記得，提取的匹配數由可選的instance_num參數控制。默認值是所有匹配項，因此您只需省略此參數：

=RegExpExtract(A2, "\d ")

該公式可用於單個單元格，但是動態陣列Excel和非動態版本的行為有所不同。

Excel 365和Excel 2021

由於支持動態陣列，常規公式會自動溢出到需要顯示所有計算結果的盡可能多的單元中。就excel而言，這稱為溢出範圍：

Excel 2019和較低

在動態前的Excel中，上述公式只會返回一場比賽。要獲得多個匹配，您需要使其成為陣列公式。為此，選擇一系列單元格，鍵入公式，然後按Ctrl Shift進入以完成它。

這種方法的缺點是在“額外的單元格”中出現了一堆＃N/A錯誤。遺憾的是，對此無能為力（iferror和Ifna都無法修復它，可惜）。

在一個單元格中提取所有匹配

處理數據列時，上述方法顯然無法正常工作。在這種情況下，理想的解決方案將返回單個單元格中的所有匹配。要完成它，請將RegexPextract的結果提供給文本Join函數，並將它們與您喜歡的任何定界符分開，例如逗號和空間：

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

筆記。由於TextJoin功能僅在Microsoft 365，Excel 2021和Excel 2019的Excel中可用，因此該公式在較舊版本中無法使用。

從字符串中提取文本的正則

在Excel中，從字母數字字符串中提取文本是一項艱鉅的任務。有了正則表達式，它變得像餡餅一樣容易。只需使用否定的類與所有不是數字的所有內容匹配。

模式：[^\ d]

為了獲得單個細胞（溢出範圍）的子字符串，公式為：

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

要將所有匹配輸出到一個單元格中，請以這樣的文字嵌套regexpextract函數：

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

從字符串中提取電子郵件地址的正則

要從包含許多不同信息的字符串中提取電子郵件地址，請編寫一個重複電子郵件地址結構的正則表達式。

模式：[\ w \。 \ - ] @[a-za-z0-9 \。 \ - ] \。 [a-za-z] {2,24}

打破了這一正則是我們得到的：

• [\ w \。 \ - ]是一個用戶名，可能包括1個或更多字母數字，下劃線，點和連字符。
• @ 象徵
• [A-ZA-Z0-9 \。 \ - ]是一個域名，由：大寫和小寫字母，數字，連字符和點（如果是子域）。在這裡不允許下劃線，因此使用3個不同的字符集（例如AZ AZ和0-9），而不是與任何字母，數字或下劃線相匹配的\ w。
• \。 [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}匹配零或一個開口括號，然後是三位數d {3}。
• 這 [-\。 \）]*零件是指在0或更多次出現的方括號中的任何字符：連字符，週期，空間或閉合括號。
• 接下來，我們再次有三位數字D {3}，然後是任何連字符，週期或空間[ - \。 ]？出現0或1次。
• 之後，有一組四位數\ 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為：\ D {1,2} \/\ d {1,2} \/（\ d {4} | \ d {2}））

它搜索一組1或2位數字d {1,2}，然後是斜線，然後是另一組1或2位數字，然後是斜線，然後是一組4或2位數字（\ d {4} | \ d {2 {2}）。請注意，我們首先要尋找4位數年，直到那時才有2位數年。如果我們以相反的方式編寫它，那麼多年來只會挑選前2位數字。這是因為在第一個條件在交替或結構匹配之後，未檢查其餘條件。

要檢索1-JAN-21或01-JAN-2021之類的日期，模式為：\ d {1,2} - [A-ZA-Z] {3} {3} - \ d \ d {2,4}

它搜索一組1或2位數字，然後是連字符，然後是一組3個大寫字母或小寫字母，然後是連字符，然後是4或2位數字的組。

將兩種模式組合在一起後，我們得到以下等級：

模式：\ b \ d {1,2} [\/ - ]（\ d {1,2} | [a-za-z] {3}）{\/ - ] [\/ - ]（\ d {4} | \ d {2}）\ b

在哪裡：

• 第一部分是1或2位數字：\ d {1,2}
• 第二部分是1或2個字母：（\ d {1,2} | [a-za-z] {3}）{3}）
• 第三部分是4或2位數字的組：（\ d {4} | \ d {2}）
• 定界符是前向斜線或連字符：[\/ - ]
• 單詞邊界\ b都放在兩邊，以表明日期是一個單獨的單詞，而不是較大字符串的一部分。

如下圖所示，它成功地拉出了日期，並將其排除在11/22/333等子字上。但是，它仍然返回假陽性結果。在我們的情況下，A9中的substring 11-ABC-2222在技術上與日期格式DD-MMM-yyyy匹配，因此提取。

為了消除誤報，您可以用3個字母的縮寫的完整列表替換[A-Za-Z] {3}部分：

模式：\ b \ d {1,2} [\/ - ]（\ d {1,2} |（Jan | feb | Mar | Mar | Apr | Apr | Apr | May | Jun | Jun | Jun | Jun | aug | aug | sep | sep | sep | oct | nock | 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 | 2 [0-3] [0-3]）

分解這條正則是|這樣可以或邏輯。換句話說，我們搜索與以下表達式之一相匹配的子字符串。

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

通過AM/PM檢索時間。

小時可以是從0到12的任何數字。要獲得它，我們使用或構造（[0-9] | 1 [0-2]），其中：

• [0-9]匹配從0到9的任何數字
• 1 [0-2]匹配10到12的任何數字

分鐘[0-5] \ d是00到59的任何數字。

第二（：[0-5] \ d）？也有任何數字從00到59。量詞是指零或一次發生，因為秒可能會或可能不包含在時間值中。

表達式2 ：\ b（[0-9] | [0-1] \ d | 2 [0-3]）：[0-5] \ d（：[0-5] \ d）？

提取無am/pm的時間。

小時部分可以是從0到32的任何數字。要獲得它，使用了不同的或構造（[0-9] | [0-1] \ d | 2 [0-3]），其中：

• [0-9]匹配從0到9的任何數字
• [0-1] \ d匹配00到19的任何數字
• 2 [0-3]匹配20到23的任何數字

分鐘和第二部分與上面的表達式1相同。

負lookahead（？！:)添加到跳過弦，例如20:30:80。

由於PM/AM可以是大寫或小寫，因此我們使功能不敏感：

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

希望以上示例為您提供了一些關於如何在Excel工作表中使用正則表達式的想法。不幸的是，VBA並非支持經典正則表達式的所有功能。如果您的任務無法通過VBA RegexP完成，我鼓勵您閱讀下一部分討論功能更強​​大的.NET REGEX功能。

基於.NET的Custom Regex功能以在Excel中提取文本

與任何Excel用戶可以編寫的VBA REGEXP函數不同，.NET REGEX是開發人員的領域。 Microsoft .NET框架支持與Perl 5兼容的完整的正則表達語法。本文不會教您如何編寫此類功能（我不是程序員，並且對如何執行此操作沒有絲毫想法。

標準.NET REGEX引擎處理的四個強大功能已經由我們的開發人員編寫，並包括在Ultimate Suite中。下面，我們將演示該功能的一些實際用途，該功能專門設計用於在Excel中提取文本。

提示。有關.NET REGEX語法的信息，請參閱.NET正則表達語言。

如何使用正則表達式在Excel中提取字符串

假設您已安裝了最新版本的Ultimate Suite，則使用正則表達式提取文本歸結為這兩個步驟：

1. 在“ ablebits數據”選項卡上，在文本組中，單擊Regex工具。

   

2. 在Regex Tools Pane上，選擇源數據，輸入您的REGEX模式，然後選擇“提取”選項。要將結果作為自定義功能而不是值，請選擇插入作為公式複選框。完成後，單擊“提取”按鈕。

   

結果將出現在您原始數據右側的新列中：

ablebitsregexextract語法

我們的自定義功能具有以下語法：

Ablebitsregexextract（參考，常規_expression）

在哪裡：

• 參考（必需） - 對包含源字符串的單元格的引用。
• 常規_ expression （必需） - 要匹配的正則表達式。

重要說明！該函數僅在機器上使用，並安裝了Excel的Ultimate Suite。

用法註釋

要使您的學習曲線更加順暢，並且您的體驗更加愉快，請注意以下要點：

1. 要創建公式，您可以使用我們的正則工具，也可以使用Excel的插入功能對話框，或在單元格中鍵入完整功能名稱。插入公式後，您可以像任何本機公式一樣管理（編輯，複製或移動）。
2. 您在Regex Tools上輸入的模式窗格轉到2 ^nd參數。也可以將正則表達式保存在單獨的單元格中。在這種情況下，只需使用單元格引用即可進行^第二個參數。
3. 該函數提取第一個發現的匹配。
4. 默認情況下，該函數對病例敏感。對於不敏感的匹配，請使用（？i）模式。
5. 如果找不到匹配項，則返回一個錯誤。

以下是在兩個字符之間提取字符串

要在兩個字符之間獲取文字，您可以使用捕獲組或查看曲目。

假設您正在尋找在括號之間提取文本。捕獲組是最簡單的方法。

模式1 ：\ [（。*？）\]

憑藉積極的外觀和lookahead，結果將完全相同。

模式2 ：（？

請注意，我們的捕獲組（。一個沒有問號的捕獲小組（。*）將進行貪婪的搜索，並捕獲從第一個[到最後一個]的一切。

使用A2中的模式，該公式如下：

=AblebitsRegexExtract(A5, $A$2)

如何獲得所有比賽

如前所述，Ablebitsregexextract功能只能提取一個匹配。要獲得所有匹配，您可以使用我們之前討論過的VBA功能。但是，有一個警告-VBA RegeXP不支持捕獲組，因此上述模式也將返回“邊界”字符，在我們的情況下，括號。

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

要擺脫支架，請使用此公式用空字符串（“”）代替它們：

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

為了獲得更好的可讀性，我們正在使用逗號進行定界符。

正則提取兩個字符串之間的文本

我們為在兩個字符之間拉出文本的方法還可以在兩個字符串之間提取文本。

例如，要在“測試1”和“測試2”之間獲得所有內容，請使用以下正則表達式。

模式：測試1（。*？）測試2

完整的公式是：

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

從URL提取域

即使使用正則表達式，從URL中提取域名也不是一項瑣碎的任務。解決問題的關鍵要素是非捕捉組。根據您的最終目標，請選擇以下以下一條。

獲取一個完整的域名，包括子域

模式：（？：https？\：|^| \ s）\/\/（（？

要獲得一個沒有子域的第二級域

模式：（？：https？\：|^| \ s）\/\/（？

現在，讓我們看看這些正則表達式如何在“ https://www.mobile.able.ablebits.com”的示例中作為示例URL：

• （？：https？\：|^| \ s） - 非捕捉組。它匹配但不捕獲之前有以下一個的子字符串：https，http，string（^）的啟動（^），一個whitespace字符（\ s）。包括最後兩個項目以處理“ //google.com”之類的協議相關URL。
• \/ \/ - 兩個前向斜線（每個斜線之前都有一個後斜切，以逃避前向斜線的特殊含義並從字面上解釋它）。
• （？：[a-za-z \ d \ - \。] {2,255} \。）？ - 非捕獲組以識別第三級，第四級等。域，如果有的話（我們的示例URL中的移動設備）。在第一個模式中，將其放置在較大的捕獲組中，以將所有此類子域納入提取中。子域可以長2到255個字符，因此{2,255}量詞。
• （[a-za-z \ d \ - ] {1,63} \。[a-za-z] {2,24}） - 捕獲組以提取第二級域（ ablebits ）和頂級域（ com ）。第二級域的最大長度為63個字符。當前存在的最長頂級域包含24個字符。

根據A2中的正則表達式，以下公式將產生不同的結果：

=AblebitsRegexExtract(A5, $A$2)

將所有子域提取完整域名的正則命名：

以下是提取沒有子域的二級域：

這就是如何使用正則表達式在Excel中提取文本的部分。我感謝您閱讀，並期待下週在我們的博客上與您見面！

可用下載

Excel Regex提取示例（.XLSM文件）Ultimate Suite試用版（.EXE文件）

以上是正則提取在Excel中提取字符串（一個或所有匹配）的詳細內容。更多資訊請關注PHP中文網其他相關文章！