Excel Regex示例：使用公式中的正則表達式

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

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

• 什麼是正則是什麼？
• Excel中是否有正則功能？
• Excel Regex備忘單
• 自定義VBA REGEX功能
• 正則匹配功能
• REGEX提取功能
• 正則替換功能
• REGEX工具 - 不帶VBA的Excel中的正則表達式

什麼是正則表達？

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

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

正則表達式用於許多編程語言，包括JavaScript和VBA。後者有一個特殊的Regexp對象，我們將使用該對象來創建我們的自定義功能。

Excel支持REGEX嗎？

遺憾的是，Excel中沒有內置的正則函數。為了能夠在公式中使用正則表達式，您必須創建自己的用戶定義功能（基於VBA或.NET）或安裝支持Regexes的第三方工具。

Excel Regex備忘單

無論是正則模式非常簡單還是非常複雜，它都是使用通用語法構建的。本教程並非旨在教您正則表達式。為此，在線有很多資源，從初學者的免費教程到高級用戶的高級課程。

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

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

人物

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

圖案	描述	例子	比賽
。	通配符角色：匹配除換行符以外的任何單個角色	.ot	點，熱，鍋， @ot
\ d	數字字符：從0到9的任何一位數字	\ d	在A1B中，匹配1
\ d	任何不是數字的角色	\ d	在A1B中，匹配A和B
\ s	空格角色：空間，標籤，新線路和馬車返回	。 \ s。	在3美分中，匹配3 C
\ s	任何非Whitespace字符	\ s	在30美分中，匹配30美分和美分
\ w	單詞字符：任何ASCII字母，數字或下劃線	\ w	在5_cats ***中，匹配5_cats
\ w	任何不是字母數字或下劃線的角色	\ w	在5_cats ***中，匹配***
\ t	選項卡
\ n	新線	\ n \ d	在下面的兩行字符串中，匹配10 5隻貓10隻狗
\ \	逃脫角色的特殊含義，因此您可以搜索它	\。 \ w \。	逃脫了一個時期，因此您可以找到字面上的“”。弦中的角色先生，夫人，教授

角色類

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

圖案	描述	例子	比賽
[人物]	匹配括號中的任何單個字符	D [oi] g	狗和挖
[^字符]	匹配任何單個字符在括號中	d [^oi] g	匹配DAG，DUG ， D1G不匹配狗和挖掘
[從–到]	匹配括號之間的任何角色	[0-9] [AZ] [AZ]	任何單個數字從0到9的任何單個小寫字母任何單個大寫字母

量詞

量詞是指定要匹配的字符數的特殊表達式。量詞始終適用於字符。

圖案	描述	例子	比賽
*	零或更多事件	1a*	1，1a ， 1aa，1aaa ，等等。
	一次或多次發生	po	在鍋中，匹配PO的窮人，匹配Poo
？	零或一次發生	路	路，羅德
*？	零或更多的發生，但盡可能少	1a*？	在1A ， 1AA和1AAA中，匹配1A
？	一次或多次發生，但盡可能少	po？	在鍋和窮人中，匹配po
？	零或一次發生，但盡可能少	roa ??	在公路和桿上，搭配RO
{n}	匹配前面的模式n次	\ d {3}	正好3位數字
{n，}	匹配前面的模式n或更多次	\ d {3，}	3個或更多位數
{N，M}	匹配N和M時間之間的前面模式	\ d {3,5}	從3到5位數字

分組

分組結構用於從源字符串捕獲子字符串，因此您可以對其進行一些操作。

句法	描述	例子	比賽
（圖案）	捕獲組：捕獲匹配的子字符串並為其分配一個序數編號	（\ d）	在5隻貓和10隻狗中，捕獲5 （第1組）和10只（第2組）
（？：圖案）	非捕捉組：匹配一個組，但沒有捕獲	（\ d）（？：狗）	在5隻貓和10隻狗中，捕獲10
\ 1	第1組的內容	（\ d）\（\ d）= \ 2 \ \ 1	匹配5 10 = 10 5 ，並捕獲5和10 ，它們在捕獲組中
\ 2	第2組的內容

錨

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

錨	描述	例子	比賽
^	啟動字符串 注意：[^內括號]的意思是“不”	^\ d	字符串開始時的任意數字。 在5隻貓和10隻狗中，匹配5
$	字符串的結尾	\ D $	字符串末端的任意數字。 在10加5中給出15個，比賽15
\ b	單詞邊界	\ bjoy \ b	將喬伊作為一個單獨的詞匹配，但不愉快。
\ b	不是單詞邊界	\ bjoy \ b	匹配愉快的喜悅，但不像一個單獨的詞相匹配。

交替（或）構造

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

構造	描述	例子	比賽
|	匹配任何由垂直條隔開的單個元素	（S | SH）Ells	在她出售海殼，銷售和貝殼

環繞

當您想匹配某些或不遵循或之前的事物之前，Lookaround構造會有所幫助。這些表達式有時被稱為“零寬度斷言”或“零寬度匹配”，因為它們匹配位置而不是實際字符。

筆記。在VBA Regex風味中，不支持LookBehinds。

圖案	描述	例子	比賽
（？=）	積極的lookahead	x（？= y）	匹配表達式x時y時y（即，如果x前面有y，則匹配）
（？！）	負面的lookahead	x（？！y）	匹配表達式x如果不緊隨其後y
（？	積極的外觀	（？	匹配表達式x之前的表達式x（即，如果x後面有y）
（？	負外觀	（？	匹配表達式x當不在y之前

現在，您知道了必需品，讓我們繼續前進最有趣的部分 - 使用真實數據上的Regexes來解析字符串並找到所需的信息。如果您需要有關語法的更多詳細信息，則有關正則表達語言的Microsoft指南可能會有所幫助。

excel的自定義正則函數

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

VBA REGEXP功能如何工作

本節解釋了內部力學，對於那些想確切知道後端發生什麼的人來說可能很有趣。

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

Regexp對象具有4個屬性：

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

VBA REGEXP限制

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

Excel Regex匹配功能

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

regexpMatch（文本，模式，[match_case]）

在哪裡：

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

該函數的代碼在這裡。

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

在下面的數據集中，假設您要標識包含SKU代碼的條目。

鑑於每個SKU以2個大寫字母開頭，然後是連字符，然後是4位數字，您可以使用以下表達式匹配它們。

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

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

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

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

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

為了方便起見，您可以在單獨的單元格中輸入正則表達式，並使用絕對參考（$ a $ 2）進行模式參數。這樣可以確保當您將公式複製到其他單元格時，小區地址將保持不變：

=RegExpMatch(A5, $A$2)

要顯示您自己的文本標籤，而不是True和False，請在IF函數中nest RegexPmatch，並在value_if_true和value_if_if_false參數中指定所需的文本：

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

有關更多公式示例，請參閱：

• 如何使用正則表達式匹配字符串
• Excel數據驗證帶有正則驗證

Excel Regex提取功能

Regexpextract函數搜索與正則表達式匹配並提取所有匹配或特定匹配的子字符串。

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

在哪裡：

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

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

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

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

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

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

=RegExpExtract(A5, $A$2)

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

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

Excel Regex替換功能

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

regexpreplace（文本，模式，替換，[instance_num]，[match_case]）

在哪裡：

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

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

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

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

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

模式：\ 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")

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

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

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

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

有關更多信息，請參閱：

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

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

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

與上面討論的VBA函數不同，Ultimate Suite的功能是基於.NET的，這給出了兩個主要優勢：

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

如何在Excel中使用Regex

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

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

   

2. 在Regex工具窗格上，請執行以下操作：
   • 選擇源數據。
   • 輸入您的正則方式。
   • 選擇所需的選項：匹配，提取，刪除或更換。
   • 要將結果作為公式而不是價值，請選擇插入作為公式複選框。
   • 點擊動作按鈕。

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

   

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

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

公式出現後，您可以像任何本機公式一樣編輯，複製或移動它。

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

無需使用加載項的接口即可直接將Ablebitsregex函數直接插入單元格。以下是：

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

   

3. 定義函數的論點，就像您通常這樣做，然後單擊確定。完畢！

有關更多信息，請參閱Excel的Regex工具。

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

可用下載

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

以上是Excel Regex示例：使用公式中的正則表達式的詳細內容。更多資訊請關注PHP中文網其他相關文章！