永遠(yuǎn)無法理解為什么在Excel公式中不支持正則表達(dá)式？現(xiàn)在，它們是:)使用我們的自定義功能，您可以輕松地找到，替換，提取和刪除與特定模式相匹配的字符串。

乍一看，Excel擁有文本字符串操作所需的一切。嗯...正則表達(dá)式呢？糟糕，Excel中沒有內(nèi)置的正則函數(shù)。但是沒有人說我們不能創(chuàng)建自己的:)

什么是正則表達(dá)？

正則表達(dá)式（又名REGEX或REGEXP ）是定義搜索模式的特殊編碼字符序列。使用該模式，您可以在字符串中找到匹配的字符組合或驗(yàn)證數(shù)據(jù)輸入。如果您熟悉通配符符號，則可以將Regexes視為通配符的高級版本。

正則表達(dá)式具有自己的語法，由特殊字符，操作員和構(gòu)造組成。例如，[0-5]匹配從0到5的任何單個數(shù)字。

正則表達(dá)式用于許多編程語言，包括JavaScript和VBA。后者有一個特殊的Regexp對象，我們將使用該對象來創(chuàng)建我們的自定義功能。

Excel支持REGEX嗎？

遺憾的是，Excel中沒有內(nèi)置的正則函數(shù)。為了能夠在公式中使用正則表達(dá)式，您必須創(chuàng)建自己的用戶定義功能（基于VBA或.NET）或安裝支持Regexes的第三方工具。

Excel Regex備忘單

無論是正則模式非常簡單還是非常復(fù)雜，它都是使用通用語法構(gòu)建的。本教程并非旨在教您正則表達(dá)式。為此，在線有很多資源，從初學(xué)者的免費(fèi)教程到高級用戶的高級課程。

在下面，我們可以快速參考主要的正則表達(dá)式模式，以幫助您掌握基礎(chǔ)知識。在研究進(jìn)一步的例子時，它也可以用作您的備忘單。

如果您對正則表達(dá)式感到滿意，則可以直接跳到Regexp功能。

人物

這些是匹配某些字符的最常用模式。

角色類

使用這些模式，您可以匹配不同字符集的元素。

量詞

量詞是指定要匹配的字符數(shù)的特殊表達(dá)式。量詞始終適用于字符。

分組

分組結(jié)構(gòu)用于從源字符串捕獲子字符串，因此您可以對其進(jìn)行一些操作。

錨

錨指定輸入字符串中的位置，以查找匹配項(xiàng)。

交替（或）構(gòu)造

交替操作數(shù)啟用或邏輯，因此您可以匹配此元素或該元素。

環(huán)繞

當(dāng)您想匹配某些或不遵循或之前的事物之前，Lookaround構(gòu)造會有所幫助。這些表達(dá)式有時被稱為“零寬度斷言”或“零寬度匹配”，因?yàn)樗鼈兤ヅ湮恢枚皇菍?shí)際字符。

筆記。在VBA Regex風(fēng)味中，不支持LookBehinds。

現(xiàn)在，您知道了必需品，讓我們繼續(xù)前進(jìn)最有趣的部分 - 使用真實(shí)數(shù)據(jù)上的Regexes來解析字符串并找到所需的信息。如果您需要有關(guān)語法的更多詳細(xì)信息，則有關(guān)正則表達(dá)語言的Microsoft指南可能會有所幫助。

excel的自定義正則函數(shù)

如前所述，Microsoft Excel沒有內(nèi)置的正則函數(shù)。為了啟用正則表達(dá)式，我們創(chuàng)建了三個自定義VBA函數(shù)（又稱用戶定義的函數(shù)）。您可以從下面鏈接的頁面或我們的示例工作簿中復(fù)制代碼，然后粘貼到自己的Excel文件中。

VBA REGEXP功能如何工作

本節(jié)解釋了內(nèi)部力學(xué)，對于那些想確切知道后端發(fā)生什么的人來說可能很有趣。

要開始在VBA中使用正則表達(dá)式，您需要激活Regex對象參考庫或使用CreateObject函數(shù)。為了節(jié)省您在VBA編輯器中設(shè)置參考的麻煩，我們選擇了后一種方法。

Regexp對象具有4個屬性：

• 模式- 是在輸入字符串中匹配的模式。
• 全局- 控制是在輸入字符串中找到所有匹配還是第一個匹配。在我們的功能中，將所有匹配項(xiàng)設(shè)置為真實(shí)。
• 多行- 確定是在多線字符串中還是僅在第一行中匹配跨線路斷裂的模式。在我們的代碼中，在每行搜索中都設(shè)置為真實(shí)。
• ignorecase-定義正則表達(dá)式是對病例敏感的（默認(rèn)）還是不敏感的（設(shè)置為true）。在我們的情況下，這取決于您如何配置可選的match_case參數(shù)。默認(rèn)情況下，所有功能都對病例敏感。

VBA REGEXP限制

Excel VBA實(shí)現(xiàn)了必需的正則表達(dá)方式，但它確實(shí)缺乏.NET，PERL，JAVA和其他Regex引擎中可用的許多高級功能。例如，VBA REGEXP不支持對諸如（？i）的內(nèi)聯(lián)修飾符（？i）用于不敏感的匹配或（？m）多行模式，lookBehinds，posix類，以等。

Excel Regex匹配功能

REGEXPMATCH函數(shù)搜索與正則表達(dá)式匹配的文本的輸入字符串，如果找到匹配，則返回true，否則為false。

在哪里：

• 文本（必需） - 一個或多個搜索的字符串。
• 模式（必需） - 匹配的正則表達(dá)式。
• match_case （可選） - 匹配類型。真實(shí)或省略 - 對病例敏感；錯誤 - 不敏感的情況

該函數(shù)的代碼在這里。

示例：如何使用正則表達(dá)式匹配字符串

在下面的數(shù)據(jù)集中，假設(shè)您要標(biāo)識包含SKU代碼的條目。

鑒于每個SKU以2個大寫字母開頭，然后是連字符，然后是4位數(shù)字，您可以使用以下表達(dá)式匹配它們。

模式：\ b [az] {2} - \ d {4} \ b

其中[az] {2}表示從a到z的任何2個大寫字母，而\ d {4}表示從0到9的任何4個數(shù)字。一個單詞邊界\ b表示SKU是一個單獨(dú)的單詞，而不是較大字符串的一部分。

建立模式后，開始鍵入像平常一樣的公式，函數(shù)的名稱將顯示在Excel Autocomplete建議的列表中：

假設(shè)原始字符串在A5中，則該公式如下：

=RegExpMatch(A5, "\b[AZ]{2}-\d{3}\b")

為了方便起見，您可以在單獨(dú)的單元格中輸入正則表達(dá)式，并使用絕對參考（$ a $ 2）進(jìn)行模式參數(shù)。這樣可以確保當(dāng)您將公式復(fù)制到其他單元格時，小區(qū)地址將保持不變：

=RegExpMatch(A5, $A$2)

要顯示您自己的文本標(biāo)簽，而不是True和False，請?jiān)贗F函數(shù)中nest RegexPmatch，并在value_if_true和value_if_if_false參數(shù)中指定所需的文本：

=IF(RegExpMatch(A5, $A$2), "Yes", "No")

有關(guān)更多公式示例，請參閱：

• 如何使用正則表達(dá)式匹配字符串
• Excel數(shù)據(jù)驗(yàn)證帶有正則驗(yàn)證

Excel Regex提取功能

Regexpextract函數(shù)搜索與正則表達(dá)式匹配并提取所有匹配或特定匹配的子字符串。

在哪里：

• 文本（必需） - 要搜索的文本字符串。
• 模式（必需） - 匹配的正則表達(dá)式。
• instance_num （可選） - 指示要提取的實(shí)例的序列號。如果省略，則返回所有找到的匹配項(xiàng)（默認(rèn)）。
• match_case （可選） - 定義要匹配（true還是省略）還是忽略（false）文本案例。

您可以在此處獲取功能的代碼。

示例：如何使用正則表達(dá)式提取字符串

以我們的示例進(jìn)一步，讓我們提取發(fā)票號碼。為此，我們將使用與任何7位數(shù)字匹配的非常簡單的正則等級：

模式：\ b \ d {7} \ b

將圖案放入A2中，您將通過這種緊湊而優(yōu)雅的公式完成工作：

=RegExpExtract(A5, $A$2)

如果模式匹配，則公式將提取發(fā)票號，如果找不到匹配 - 什么也不會返回。

有關(guān)更多示例，請參閱：如何使用Regex在Excel中提取字符串。

Excel Regex替換功能

Regexpreplace函數(shù)替換了與您指定的文本相匹配的值。

在哪里：

• 文本（必需） - 要搜索的文本字符串。
• 模式（必需） - 匹配的正則表達(dá)式。
• 替換（必需） - 用文本替換匹配子字符串的文本。
• instance_num （可選） - 要替換??的實(shí)例。默認(rèn)值為“所有匹配”。
• match_case （可選） - 控制要匹配（true還是省略）或忽略（false）文本案例。

該功能的代碼可在此處提供。

示例：如何使用Regexes替換或刪除字符串

我們的一些記錄包含信用卡號。此信息是機(jī)密的，您可能需要用某些東西替換或完全刪除它。這兩個任務(wù)都可以在Regexpreplace功能的幫助下完成。如何？在第二種情況下，我們將替換為一個空字符串。

在我們的示例表中，所有卡號都有16位數(shù)字，這些數(shù)字用4組寫成，這些數(shù)字是用空格分開的。為了找到它們，我們使用此正則表達(dá)式復(fù)制模式：

模式：\ b \ d {4} \ d {4} \ d {4} \ d {4} \ b

為了替換，使用以下字符串：

替換：xxxx xxxx xxxx xxxx

這是一個完整的公式，可以用不敏感的信息替換信用卡號：

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "XXXX XXXX XXXX XXXX")

在單獨(dú)的單元格（A2和B2）中的Regex和更換文本的情況下，該公式同樣效果很好：

在Excel中，“刪除”是“替換”的特殊情況。要刪除信用卡號，只需使用一個空字符串（“”）進(jìn)行替換參數(shù)：

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "")

提示。要在結(jié)果中獲取空線的鉆機(jī)，您可以使用另一個RegexPreplace功能，如以下示例所示：如何使用Regex刪除空白行。

有關(guān)更多信息，請參閱：

• 如何使用Regex替換Excel中的字符串
• 如何使用正則拆除字符串
• 如何使用Regexes剝離空格

匹配，提取，替換和刪除子字符串的正則工具

我們的Ultimate Suite的用戶可以在沒有在其工作簿中插入一行代碼的情況下獲得正則表達(dá)式的所有功能。所有必要的代碼都是由我們的開發(fā)人員編寫的，并且在安裝過程中將其集成在您的Excel中。

與上面討論的VBA函數(shù)不同，Ultimate Suite的功能是基于.NET的，這給出了兩個主要優(yōu)勢：

1. 您可以在普通.xlsx工作簿中使用正則表達(dá)式，而無需添加任何VBA代碼并必須將其保存為宏觀啟用文件。
2. 。

如何在Excel中使用Regex

安裝了Ultimate Suite后，在Excel中使用正則表達(dá)式與這兩個步驟一樣簡單：

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

   

2. 在Regex工具窗格上，請執(zhí)行以下操作：
   • 選擇源數(shù)據(jù)。
   • 輸入您的正則方式。
   • 選擇所需的選項(xiàng)：匹配，提取，刪除或更換。
   • 要將結(jié)果作為公式而不是價值，請選擇插入作為公式復(fù)選框。
   • 點(diǎn)擊動作按鈕。

   例如，要從單元格A2：A6刪除信用卡號，我們配置了這些設(shè)置：

   

在Trice中，Ablebitsregex功能將插入原始數(shù)據(jù)右側(cè)的新列中。在我們的情況下，公式是：

=AblebitsRegexRemove(A2, "\b\d{4} \d{4} \d{4} \d{4}\b")

公式出現(xiàn)后，您可以像任何本機(jī)公式一樣編輯，復(fù)制或移動它。

如何直接在單元格中插入正則公式

無需使用加載項(xiàng)的接口即可直接將Ablebitsregex函數(shù)直接插入單元格。以下是：

1. 單擊公式欄上的FX按鈕或“公式”選項(xiàng)卡上的插入功能。
2. 在“插入功能”對話框中，選擇“ ablebitsudfs”類別，選擇感興趣的函數(shù)，然后單擊“確定”。

   

3. 定義函數(shù)的論點(diǎn)，就像您通常這樣做，然后單擊確定。完畢！

有關(guān)更多信息，請參閱Excel的Regex工具。

這就是如何使用正則表達(dá)式匹配，提取，替換和刪除Excel單元格中的文本的方法。我感謝您閱讀，并期待下周在我們的博客上與您見面！

可用下載

Excel Regex-公式示例（.XLSM文件）Ultimate Suite-試用版（.EXE文件）

以上是Excel Regex示例：使用公式中的正則表達(dá)式的詳細(xì)內(nèi)容。更多信息請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！