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

Microsoft Excel提供了許多功能，可以從單元格中提取文本。這些功能可以應(yīng)對(duì)工作表中的大多數(shù)字符串提取挑戰(zhàn)。大多數(shù)，但不是全部。當(dāng)文本功能偶然發(fā)現(xiàn)時(shí)，正則表達(dá)式救援。等等…Excel沒有正則功能！是的，沒有內(nèi)置功能。但是沒有什么可以阻止您使用自己的:)

Excel VBA正則函數(shù)提取字符串

要在Excel中添加自定義的Regex提取功能，請(qǐng)?jiān)赩BA編輯器中粘貼以下代碼。為了在VBA中啟用正則表達(dá)式，我們正在使用內(nèi)置的Microsoft Regexp對(duì)象。該代碼由我們的Excel專家Alex Frolov編寫。

如果您對(duì)VBA的經(jīng)驗(yàn)很少，則分步用戶指南可能會(huì)有所幫助：如何在Excel中插入VBA代碼。

筆記。對(duì)于工作功能，請(qǐng)確保將您的文件保存為宏支持的工作簿（.XLSM）。

Regexpextract語法

Regexpextract函數(shù)搜索輸入字符串以匹配正則表達(dá)式并提取一個(gè)或所有匹配的值。

該函數(shù)具有以下語法：

在哪里：

• 文本（必需） - 要搜索的文本字符串。
• 模式（必需） - 匹配的正則表達(dá)式。當(dāng)直接以公式提供時(shí)，應(yīng)以雙引號(hào)標(biāo)記封閉該圖案。
• instance_num （可選） - 指示要提取的實(shí)例的序列號(hào)。如果省略，則返回所有找到的匹配項(xiàng)（默認(rèn)）。
• match_case （可選） - 定義是匹配還是忽略文本案例。如果true或省略（默認(rèn)），則執(zhí)行對(duì)案例敏感的匹配；如果是錯(cuò)誤的 - 不敏感的。

該功能在Excel 365，Excel 2021，Excel 2019，Excel 2016，Excel 2013和Excel 2010的所有版本中都起作用。

關(guān)于Regexpextract的4件事

要有效地使用Excel中的功能，需要注意一些重要的事情：

1. 默認(rèn)情況下，該函數(shù)將所有發(fā)現(xiàn)的匹配返回到相鄰單元格中，如本示例所示。要獲取特定的事件，請(qǐng)向instance_num參數(shù)提供相應(yīng)的數(shù)字。
2. 默認(rèn)情況下，該函數(shù)對(duì)病例敏感。對(duì)于對(duì)案例不敏感的匹配，將match_case參數(shù)設(shè)置為false。由于VBA的限制，對(duì)案例不敏感的結(jié)構(gòu)（？i）無法正常工作。
3. 如果找不到有效的模式，則該函數(shù)將不返回（空字符串）。
4. 如果圖案無效，則為#Value！發(fā)生錯(cuò)誤。

在開始在工作表中使用此自定義功能之前，您需要了解它的能力，對(duì)嗎？以下示例涵蓋了一些常見用例，并解釋了為什么動(dòng)態(tài)陣列Excel（Microsoft 365和Excel 2021）和傳統(tǒng)Excel（2019及以上版本）的行為可能有所不同。

筆記。罰款示例是為童話簡單數(shù)據(jù)集編寫的。我們不能保證它們?cè)谀恼鎸?shí)工作表中會(huì)完美工作。那些擁有正則經(jīng)驗(yàn)的人會(huì)同意，編寫正則表達(dá)方式是一條永無止境的完美之路 - 幾乎總是有一種方法使其更優(yōu)雅或能夠處理更廣泛的輸入數(shù)據(jù)。

從字符串中提取數(shù)字的正則

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

您要決定的第一件事是要檢索哪個(gè)數(shù)字：首先，最后，特定的發(fā)生或所有數(shù)字。

提取第一個(gè)數(shù)字

這很簡單，就像發(fā)行正則可以得到的那樣。鑒于\ d表示從0到9的任何數(shù)字，而指的是一次或多次，我們的正則表達(dá)式采用此形式：

模式：\ d

將instance_num設(shè)置為1，您將獲得所需的結(jié)果：

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

其中A5是原始字符串。

為了方便起見，您可以將圖案輸入預(yù)定義的單元格（$ a $ 2），并用$標(biāo)志鎖定其地址：

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

獲取最后一個(gè)數(shù)字

要在字符串中提取最后一個(gè)數(shù)字，以下是要使用的模式：

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

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

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

尖端：

• 要獲得特定的發(fā)生，請(qǐng)使用\ d進(jìn)行模式，并為實(shí)例_num使用適當(dāng)?shù)男蛄刑?hào)。
• 在下一個(gè)示例中討論了提取所有數(shù)字的公式。

提取所有匹配的正則

進(jìn)一步推動(dòng)我們的示例，假設(shè)您想從字符串中獲取所有數(shù)字，而不僅僅是一個(gè)。

您可能還記得，提取的匹配數(shù)由可選的instance_num參數(shù)控制。默認(rèn)值是所有匹配項(xiàng)，因此您只需省略此參數(shù)：

=RegExpExtract(A2, "\d ")

該公式可用于單個(gè)單元格，但是動(dòng)態(tài)陣列Excel和非動(dòng)態(tài)版本的行為有所不同。

Excel 365和Excel 2021

由于支持動(dòng)態(tài)陣列，常規(guī)公式會(huì)自動(dòng)溢出到需要顯示所有計(jì)算結(jié)果的盡可能多的單元中。就excel而言，這稱為溢出范圍：

Excel 2019和較低

在動(dòng)態(tài)前的Excel中，上述公式只會(huì)返回一場比賽。要獲得多個(gè)匹配，您需要使其成為陣列公式。為此，選擇一系列單元格，鍵入公式，然后按Ctrl Shift進(jìn)入以完成它。

這種方法的缺點(diǎn)是在“額外的單元格”中出現(xiàn)了一堆＃N/A錯(cuò)誤。遺憾的是，對(duì)此無能為力（iferror和Ifna都無法修復(fù)它，可惜）。

在一個(gè)單元格中提取所有匹配

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

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

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

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

在Excel中，從字母數(shù)字字符串中提取文本是一項(xiàng)艱巨的任務(wù)。有了正則表達(dá)式，它變得像餡餅一樣容易。只需使用否定的類與所有不是數(shù)字的所有內(nèi)容匹配。

模式：[^\ d]

為了獲得單個(gè)細(xì)胞（溢出范圍）的子字符串，公式為：

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

要將所有匹配輸出到一個(gè)單元格中，請(qǐng)以這樣的文字嵌套regexpextract函數(shù)：

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

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

要從包含許多不同信息的字符串中提取電子郵件地址，請(qǐng)編寫一個(gè)重復(fù)電子郵件地址結(jié)構(gòu)的正則表達(dá)式。

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

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

• [\ w \。\ - ]是一個(gè)用戶名，可能包括1個(gè)或更多字母數(shù)字，下劃線，點(diǎn)和連字符。
• @ 象征
• [A-ZA-Z0-9 \。\ - ]是一個(gè)域名，由：大寫和小寫字母，數(shù)字，連字符和點(diǎn)（如果是子域）。在這里不允許下劃線，因此使用3個(gè)不同的字符集（例如AZ AZ和0-9），而不是與任何字母，數(shù)字或下劃線相匹配的\ w。
• \。[A-ZA-Z] {2,24}是頂級(jí)域。由一個(gè)點(diǎn)組成，然后是大寫和小寫字母。大多數(shù)頂級(jí)域是3個(gè)字母長（例如.com .org，.edu等），但從理論上講，它可以包含2到24個(gè)字母（最長的注冊(cè)tld）。

假設(shè)字符串為A5，并且在A2中的模式，則提取電子郵件地址的公式為：

=RegExpExtract(A5, $A$2)

從電子郵件中提取域的正則

在提取電子郵件域時(shí)，想到的第一個(gè)想法是使用捕獲組來查找緊隨 @字符的文本。

模式： @（[A-ZA-Z0-9 \。\ - ] \。[A-ZA-Z] {2,24}）

將其提供給我們的REGEXP功能：

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

您會(huì)得到這個(gè)結(jié)果：

借助經(jīng)典的正則表達(dá)式，提取中不包括捕獲組以外的任何東西。沒有人知道為什么VBA Regex的工作方式有所不同，并且也捕獲了“@”。為了擺脫它，您可以通過用空字符串替換結(jié)果從結(jié)果中刪除第一個(gè)字符。

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

提取電話號(hào)碼的正則表達(dá)式

電話號(hào)碼可以以多種不同的方式編寫，這就是為什么在任何情況下都無法提出解決方案的原因。但是，您可以寫下數(shù)據(jù)集中使用的所有格式并嘗試匹配它們。

在此示例中，我們將創(chuàng)建一個(gè)正則是以任何以下格式提取電話號(hào)碼的正則號(hào)：

模式：\（？\ d {3} [ - \。\）]*\ d {3} [ - \。 ]？\ d {4} \ b

• 第一部分\（？\ d {3}匹配零或一個(gè)開口括號(hào)，然后是三位數(shù)d {3}。
• 這 [-\。 \）]*零件是指在0或更多次出現(xiàn)的方括號(hào)中的任何字符：連字符，周期，空間或閉合括號(hào)。
• 接下來，我們?cè)俅斡腥粩?shù)字D {3}，然后是任何連字符，周期或空間[ - \。 ]？出現(xiàn)0或1次。
• 之后，有一組四位數(shù)\ d {4}。
• 最后，有一個(gè)單詞邊界\ b定義我們正在尋找的電話號(hào)碼不能成為更大數(shù)字的一部分。

完整的公式采用這種形狀：

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

請(qǐng)注意，上述正則返回一些虛假陽性結(jié)果，例如123）456 7899或（123 456 7899。以下版本解決了這些問題。但是，該語法僅在VBA RegexP函數(shù)中起作用，而不是在經(jīng)典正則表達(dá)式中。

模式：（\（\ d {3} \）| \ d {3}）[ - \。 ]？\ d {3} [ - \。 ]？\ d {4} \ b

從字符串提取日期

提取日期的正則表達(dá)式取決于日期出現(xiàn)在字符串中的格式。例如：

要提取如1/1/21或01/01/2021之類的日期，REGEX為：\ D {1,2} \/\ d {1,2} \/（\ d {4} | \ d {2}））

它搜索一組1或2位數(shù)字d {1,2}，然后是斜線，然后是另一組1或2位數(shù)字，然后是斜線，然后是一組4或2位數(shù)字（\ d {4} | \ d {2 {2}）。請(qǐng)注意，我們首先要尋找4位數(shù)年，直到那時(shí)才有2位數(shù)年。如果我們以相反的方式編寫它，那么多年來只會(huì)挑選前2位數(shù)字。這是因?yàn)樵诘谝粋€(gè)條件在交替或結(jié)構(gòu)匹配之后，未檢查其余條件。

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

它搜索一組1或2位數(shù)字，然后是連字符，然后是一組3個(gè)大寫字母或小寫字母，然后是連字符，然后是4或2位數(shù)字的組。

將兩種模式組合在一起后，我們得到以下等級(jí)：

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

在哪里：

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

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

為了消除誤報(bào)，您可以用3個(gè)字母的縮寫的完整列表替換[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

為了忽略字母案例，我們將自定義函數(shù)的最后一個(gè)參數(shù)設(shè)置為false：

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

這次，我們得到了一個(gè)完美的結(jié)果：

從字符串中提取時(shí)間

要在HH中獲得時(shí)間：MM或HH：MM：SS格式，以下表達(dá)式將有效。

模式：\ 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]）

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

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

通過AM/PM檢索時(shí)間。

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

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

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

第二（：[0-5] \ d）？也有任何數(shù)字從00到59。量詞是指零或一次發(fā)生，因?yàn)槊肟赡軙?huì)或可能不包含在時(shí)間值中。

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

提取無am/pm的時(shí)間。

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

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

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

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

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

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

希望以上示例為您提供了一些關(guān)于如何在Excel工作表中使用正則表達(dá)式的想法。不幸的是，VBA并非支持經(jīng)典正則表達(dá)式的所有功能。如果您的任務(wù)無法通過VBA RegexP完成，我鼓勵(lì)您閱讀下一部分討論功能更強(qiáng)??大的.NET REGEX功能。

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

與任何Excel用戶可以編寫的VBA REGEXP函數(shù)不同，.NET REGEX是開發(fā)人員的領(lǐng)域。 Microsoft .NET框架支持與Perl 5兼容的完整的正則表達(dá)語法。本文不會(huì)教您如何編寫此類功能（我不是程序員，并且對(duì)如何執(zhí)行此操作沒有絲毫想法。

標(biāo)準(zhǔn).NET REGEX引擎處理的四個(gè)強(qiáng)大功能已經(jīng)由我們的開發(fā)人員編寫，并包括在Ultimate Suite中。下面，我們將演示該功能的一些實(shí)際用途，該功能專門設(shè)計(jì)用于在Excel中提取文本。

提示。有關(guān).NET REGEX語法的信息，請(qǐng)參閱.NET正則表達(dá)語言。

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

假設(shè)您已安裝了最新版本的Ultimate Suite，則使用正則表達(dá)式提取文本歸結(jié)為這兩個(gè)步驟：

1. 在“ ablebits數(shù)據(jù)”選項(xiàng)卡上，在文本組中，單擊Regex工具。

   

2. 在Regex Tools Pane上，選擇源數(shù)據(jù)，輸入您的REGEX模式，然后選擇“提取”選項(xiàng)。要將結(jié)果作為自定義功能而不是值，請(qǐng)選擇插入作為公式復(fù)選框。完成后，單擊“提取”按鈕。

   

結(jié)果將出現(xiàn)在您原始數(shù)據(jù)右側(cè)的新列中：

ablebitsregexextract語法

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

在哪里：

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

重要說明！該函數(shù)僅在機(jī)器上使用，并安裝了Excel的Ultimate Suite。

用法注釋

要使您的學(xué)習(xí)曲線更加順暢，并且您的體驗(yàn)更加愉快，請(qǐng)注意以下要點(diǎn)：

1. 要?jiǎng)?chuàng)建公式，您可以使用我們的正則工具，也可以使用Excel的插入功能對(duì)話框，或在單元格中鍵入完整功能名稱。插入公式后，您可以像任何本機(jī)公式一樣管理（編輯，復(fù)制或移動(dòng)）。
2. 您在Regex Tools上輸入的模式窗格轉(zhuǎn)到2 ^nd參數(shù)。也可以將正則表達(dá)式保存在單獨(dú)的單元格中。在這種情況下，只需使用單元格引用即可進(jìn)行第二^個(gè)參數(shù)。
3. 該函數(shù)提取第一個(gè)發(fā)現(xiàn)的匹配。
4. 默認(rèn)情況下，該函數(shù)對(duì)病例敏感。對(duì)于不敏感的匹配，請(qǐng)使用（？i）模式。
5. 如果找不到匹配項(xiàng)，則返回一個(gè)錯(cuò)誤。

以下是在兩個(gè)字符之間提取字符串

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

假設(shè)您正在尋找在括號(hào)之間提取文本。捕獲組是最簡單的方法。

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

憑借積極的外觀和lookahead，結(jié)果將完全相同。

模式2 ：（？

請(qǐng)注意，我們的捕獲組（。一個(gè)沒有問號(hào)的捕獲小組（。*）將進(jìn)行貪婪的搜索，并捕獲從第一個(gè)[到最后一個(gè)]的一切。

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

=AblebitsRegexExtract(A5, $A$2)

如何獲得所有比賽

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

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

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

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

為了獲得更好的可讀性，我們正在使用逗號(hào)進(jìn)行定界符。

正則提取兩個(gè)字符串之間的文本

我們?yōu)樵趦蓚€(gè)字符之間拉出文本的方法還可以在兩個(gè)字符串之間提取文本。

例如，要在“測試1”和“測試2”之間獲得所有內(nèi)容，請(qǐng)使用以下正則表達(dá)式。

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

完整的公式是：

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

從URL提取域

即使使用正則表達(dá)式，從URL中提取域名也不是一項(xiàng)瑣碎的任務(wù)。解決問題的關(guān)鍵要素是非捕捉組。根據(jù)您的最終目標(biāo)，請(qǐng)選擇以下以下一條。

獲取一個(gè)完整的域名，包括子域

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

要獲得一個(gè)沒有子域的第二級(jí)域

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

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

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

根據(jù)A2中的正則表達(dá)式，以下公式將產(chǎn)生不同的結(jié)果：

=AblebitsRegexExtract(A5, $A$2)

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

以下是提取沒有子域的二級(jí)域：

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

可用下載

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

以上是正則提取在Excel中提取字符串（一個(gè)或所有匹配）的詳細(xì)內(nèi)容。更多信息請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！