您是否曾經(jīng)想過(guò)，如果有人可以用正則表達(dá)式豐富其工具箱，那麼Excel會(huì)有多強(qiáng)大？我們不僅想到了，而且努力了:)現(xiàn)在，您可以在自己的工作簿中添加這個(gè)出色的Regex功能，並立即擦除與圖案相匹配的子字樣！

上週，我們研究瞭如何使用正則表達(dá)式替換Excel中的字符串。為此，我們創(chuàng)建了一個(gè)自定義的正則替換功能。事實(shí)證明，該功能超出了其主要用途，不僅可以替換字符串，還可以刪除它們。那怎麼可能？就excel而言，刪除值只不過(guò)是用一個(gè)空字符串代替它，這是我們的Regex功能非常擅長(zhǎng)的！

VBA REGEXP函數(shù)以刪除Excel中的子字符串

眾所周知，默認(rèn)情況下，Excel不支持正則表達(dá)式。要啟用它們，您需要?jiǎng)?chuàng)建自己的用戶(hù)定義功能。好消息是，這種功能已經(jīng)編寫(xiě)，測(cè)試和準(zhǔn)備使用。您要做的就是複制此代碼，將其粘貼到VBA編輯器中，然後將文件保存為宏支持的工作簿（.xlsm）。

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

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

需要前三個(gè)參數(shù)，最後兩個(gè)是可選的。

在哪裡：

• 文本- 要搜索的文本字符串。
• 模式- 要搜索的正則表達(dá)式。
• 替換- 要替換??的文本。要刪除與圖案匹配的子字符串，請(qǐng)使用一個(gè)空字符串（“”）進(jìn)行更換。
• instance_num （可選） - 要替換??的實(shí)例。如果省略，所有發(fā)現(xiàn)的匹配都將更換（默認(rèn)）。
• match_case （可選） - 一個(gè)布爾值，指示是匹配還是忽略文本案例。對(duì)於對(duì)案例敏感的匹配，請(qǐng)使用true（默認(rèn)）;對(duì)於案例不敏感的 - 錯(cuò)誤。

有關(guān)更多信息，請(qǐng)參閱Regexpreplace功能。

提示。在簡(jiǎn)單的情況下，您可以使用Excel公式從單元格中刪除特定字符或單詞。但是正則表達(dá)式為此提供了更多選擇。

如何使用正則表達(dá)式刪除字符串 - 示例

如上所述，要?jiǎng)h除與圖案匹配的文本的一部分，您將用空字符串替換它們。因此，通用公式採(cǎi)用這種形狀：

regexpreplace（文本，模式，“”，[instance_num]，[match_case]）

以下示例顯示了此基本概念的各種實(shí)現(xiàn)。

刪除所有匹配或特定匹配

Regexpreplace函數(shù)旨在查找與給定的正則匹配的所有子字符串。要?jiǎng)h除的事件由第4^個(gè)可選參數(shù)（名為instance_num）控制。

默認(rèn)值為“所有匹配” - 當(dāng)省略了instance_num參數(shù)時(shí)，所有發(fā)現(xiàn)的匹配將刪除。要?jiǎng)h除特定的匹配，請(qǐng)定義實(shí)例號(hào)。

在以下字符串中，假設(shè)您要?jiǎng)h除第一階號(hào)。所有此類(lèi)數(shù)字從哈希符號(hào)（＃）開(kāi)始，並完全包含5位數(shù)字。因此，我們可以使用以下等級(jí)來(lái)識(shí)別它們：

模式：＃\ d {5} \ b

單詞邊界\ b指定匹配的子字符串不能是諸如＃10000001之類(lèi)的較大字符串的一部分。

要?jiǎng)h除所有匹配項(xiàng)，未定義實(shí)例_num參數(shù)：

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

要僅消除第一次出現(xiàn)，我們將instance_num參數(shù)設(shè)置為1：

=RegExpReplace(A5, "#\d{5}\b", "", 1)

刪除某些字符的正則

要從字符串中剝離某些字符，只需寫(xiě)下所有不需要的字符，然後用垂直條將它們分開(kāi)|在Regexes中充當(dāng)或操作員。

例如，為了標(biāo)準(zhǔn)以各種格式編寫(xiě)的電話號(hào)碼，首先我們要擺脫特定字符，例如括號(hào)，連字符，點(diǎn)和空格。

模式：\（| \）| - | \。 | \ s

=RegExpReplace(A5, "\(|\)|-|\.|\s", "")

此操作的結(jié)果是一個(gè)10位數(shù)字，例如“ 1234567890”。

為了方便起見(jiàn)，您可以輸入正則是一個(gè)單獨(dú)的單元格，並使用絕對(duì)參考（例如$ a $ 2：）參考該單元格。

=RegExpReplace(A5, $A$2, "")

然後，您可以使用串聯(lián)操作員（＆）以及右，中和左的文本功能標(biāo)準(zhǔn)化所需的格式。

例如，要在（123）456-7890格式中寫(xiě)下所有電話號(hào)碼，公式為：

="("&LEFT(B5, 3)&") "&MID(B5, 4, 3)&"-"&RIGHT(B5, 4)

其中B5是Regexpreplace函數(shù)的輸出。

使用正則刪除特殊字符

在我們的一個(gè)教程中，我們研究瞭如何使用內(nèi)置和自定義功能在Excel中刪除不必要的字符。正則表達(dá)使事情變得容易得多！而不是列出要?jiǎng)h除的所有字符，而只需指定要保留的字符:)

該模式基於否定的字符類(lèi)- 將Caret放置在字符類(lèi)中[^]，以匹配任何不括號(hào)中的單個(gè)字符。量詞迫使其將連續(xù)的字符視為單個(gè)匹配，以便為匹配的子字符串而不是每個(gè)單獨(dú)的字符進(jìn)行替換。

根據(jù)您的需求，請(qǐng)選擇以下言論之一。

要?jiǎng)h除非α數(shù)字字符，即除字母和數(shù)字以外的所有字符：

模式：[^0-9A-ZA-Z]

清除除字母，數(shù)字和空間以外的所有字符：

模式：[^0-9A-ZA-Z]

要?jiǎng)h除除字母，數(shù)字和下劃線以外的所有字符，您可以使用代表任何不是字母數(shù)字字符或下劃線的字符的\ w：

模式：\ w

如果您想保留其他一些字符，例如標(biāo)點(diǎn)符號(hào)，請(qǐng)將它們放在括號(hào)內(nèi)。

例如，要?jiǎng)冸x字母，數(shù)字，週期，逗號(hào)或空間以外的任何字符，請(qǐng)使用以下正則言論：

模式：[^0-9a-Za-Z \。 ，]

這成功消除了所有特殊角色，但額外的空格仍然存在。

為了解決此問(wèn)題，您可以將上述功能嵌套到另一個(gè)函數(shù)中，該功能將多個(gè)空格替換為單個(gè)空間字符。

=RegExpReplace(RegExpReplace(A5,$A$2,""), " ", " ")

或者只使用具有相同效果的本機(jī)內(nèi)飾函數(shù)：

=TRIM(RegExpReplace(A5, $A$2, ""))

刪除非數(shù)字字符的正則

要從字符串中刪除所有非數(shù)字字符，您可以使用此長(zhǎng)公式或下面列出的非常簡(jiǎn)單的Regexes之一。

匹配任何不是數(shù)字的角色：

模式：\ d

使用否定類(lèi)剝離非數(shù)字字符：

模式：[^0-9]

模式：[^\ d]

提示。如果您的目標(biāo)是刪除文本並將剩餘的數(shù)字溢出到單獨(dú)的單元格中，或?qū)⑺鼈內(nèi)糠旁谝粋€(gè)用指定的定界符分離的單元中，則使用Regexpextract函數(shù)，如如何使用正則表達(dá)式從字符串中提取數(shù)字中所述。

正則刪除所有事物

要在空間之後擦除所有內(nèi)容，請(qǐng)使用空間（）或whitespace（\ s）字符查找第一個(gè)空間和。 *以匹配其之後的任何字符。

如果您的單線字符串僅包含正常空間（在7位ASCII系統(tǒng)中值32），則您使用的以下式以下言論中的哪一個(gè)並不重要。在多行字符串的情況下，確實(shí)有所作為。

要在空間字符之後刪除所有內(nèi)容，請(qǐng)使用此正則態(tài)度：

圖案： ” 。*”

=RegExpReplace(A5, " .*", "")

此公式將在每行第一個(gè)空間之後剝離任何東西。為了正確顯示結(jié)果，請(qǐng)確保打開(kāi)包裹文本。

要在空格之後剝離所有內(nèi)容（包括空間，標(biāo)籤，馬車(chē)返回和新線路），正則是：

模式：\ s。 *

=RegExpReplace(A5, "\s.*", "")

因?yàn)閈 s匹配了幾種不同的空格類(lèi)型，包括新行（\ n），因此該公式在單元格中第一個(gè)空間之後刪除了所有內(nèi)容，無(wú)論其中有多少行。

正則刪除特定字符的文字

使用上一個(gè)示例中的方法，您可以在指定的任何字符之後根除文本。

分別處理每條線：

通用模式：char。 *

在單線字符串中，這將在字符之後刪除所有內(nèi)容。在多行字符串中，每行將被單獨(dú)處理，因?yàn)樵赩BA Regex風(fēng)味中，一個(gè)（。）匹配除了新線之外的任何字符。

作為單個(gè)字符串處理所有行：

通用模式：char（。| \ n）*

要在給定字符之後刪除任何內(nèi)容，包括新行，\ n將添加到模式中。

例如，要在字符串中的第一個(gè)逗號(hào)之後刪除文本，請(qǐng)嘗試以下正則表達(dá)式：

圖案： ，。 *

模式：，（。| \ n）*

在下面的屏幕截圖中，您可以檢查結(jié)果的不同。

正則要在空間之前刪除所有內(nèi)容

使用長(zhǎng)文本時(shí)，您有時(shí)可能需要通過(guò)刪除所有單元格中的相同部分來(lái)使其更短。下面我們將討論兩個(gè)這樣的情況。

在最後一個(gè)空間之前刪除所有內(nèi)容

與上一個(gè)示例一樣，正則表達(dá)式取決於您對(duì)“空間”的理解。

為了將任何內(nèi)容匹配到最後一個(gè)空間，此正則是這樣的（添加了引號(hào)標(biāo)記以在明顯的星號(hào)之後添加一個(gè)空間）。

圖案： ”。* ”

要匹配最後一個(gè)空格之前的任何內(nèi)容（包括空間，標(biāo)籤，托架返回和新線路），請(qǐng)使用此正則表達(dá)式。

模式：。 *\ s

在多行字符串上，差異尤其明顯。

在第一個(gè)空間之前剝離一切

要將任何內(nèi)容匹配到字符串中的第一個(gè)空間，您可以使用此正則表達(dá)式：

模式： ^ [ ^]*

從字符串的開(kāi)頭 ^，我們匹配零或多個(gè)非空間字符[ ^]*，後者緊隨其後的一個(gè)或多個(gè)空格“”。添加最後一部分是為了防止結(jié)果中的潛在領(lǐng)先空間。

要在每行的第一個(gè)空間之前刪除文本，該公式以默認(rèn)的“所有匹配”模式寫(xiě)入（省略了）：

=RegExpReplace(A5, "^[^ ]* ", "")

要在第一行的第一個(gè)空間之前刪除文本，然後將所有其他行留下完整，請(qǐng)將instance_num參數(shù)設(shè)置為1：

=RegExpReplace(A5, "^[^ ]* ", "", 1)

正則剝奪角色之前的一切

在特定字符之前，使用這樣的正則以下的正則刪除所有文本的最簡(jiǎn)單方法：

通用模式： ^[ ^char]*char

它翻譯成一種人類(lèi)語(yǔ)言，它說(shuō)：“從錨定的字符串開(kāi)始，匹配0或更多字符，除了char [ ^char]*直到char的第一次出現(xiàn)。

例如，要在第一個(gè)結(jié)腸之前刪除所有文本，請(qǐng)使用此正則表達(dá)式：

模式： ^[ ^：]*：

為避免結(jié)果中的前導(dǎo)空間，請(qǐng)?jiān)谀┪蔡砑右粋€(gè)空格字符\ s*。這將在第一個(gè)結(jié)腸之前刪除所有內(nèi)容，並在其之後修剪任何空間：

模式： ^[ ^：]*：\ s*

=RegExpReplace(A5, "^[^:]*:\s*", "")

提示。除了正則表達(dá)式外，Excel還有自己的手段，可以按位置或匹配刪除文本。要了解如何使用本機(jī)公式完成任務(wù)，請(qǐng)查看如何在Excel中的角色之前或之後刪除文本。

以刪除所有內(nèi)容以外的一切

除了要保留的字符串之外，使用否定的字符類(lèi)，從字符串中消除所有字符。

例如，要?jiǎng)h除除小寫(xiě)字母和點(diǎn)以外的所有字符，正則是：

模式：[^az \。 ]

實(shí)際上，我們可以在此處沒(méi)有量詞的情況下做，因?yàn)槲覀兊暮瘮?shù)替代了所有發(fā)現(xiàn)的匹配。量詞的速度更快 - 而不是處理每個(gè)字符，而是替換一個(gè)子字符串。

=RegExpReplace(A5, "[^az\.] ", "")

正則刪除Excel中的HTML標(biāo)籤

首先，應(yīng)該注意的是，HTML不是一種常規(guī)語(yǔ)言，因此使用正則表達(dá)式對(duì)其進(jìn)行解析不是最好的方法。也就是說(shuō)，Regexes絕對(duì)可以幫助從細(xì)胞中剝離標(biāo)籤，以使您的數(shù)據(jù)集更清潔。

鑑於始終將HTML標(biāo)籤放置在角度括號(hào)內(nèi)，您可以使用以下言論之一找到它們。

否定課：

模式：]*>

在這裡，我們匹配一個(gè)開(kāi)頭角括號(hào)，然後將任何字符的零或更多出現(xiàn)，除了閉合角括號(hào)[^>]*直至最近的閉合角括號(hào)。

懶惰搜索：

模式：

在這裡，我們匹配了從第一個(gè)開(kāi)放式支架到第一個(gè)關(guān)閉支架的任何內(nèi)容。問(wèn)號(hào)力。 *要匹配盡可能少的字符，直到找到一個(gè)結(jié)束括號(hào)為止。

無(wú)論您選擇哪種模式，結(jié)果將絕對(duì)相同。

例如，要從A5中的字符串中刪除所有HTML標(biāo)籤並留下文本，該公式為：

=RegExpReplace(A5, "]*>", "")

或者，您可以使用屏幕截圖中所示的懶惰量詞：

該解決方案非常適合單文本（第5-9行）。對(duì)於多個(gè)文本（第10-12行），結(jié)果值得懷疑 - 來(lái)自不同標(biāo)籤的文本被合併為一個(gè)。這是正確的嗎？恐怕，這不是很容易決定的東西 - 所有這些都取決於您對(duì)所需結(jié)果的理解。例如，在B11中，預(yù)期結(jié)果“ A1”。在B10中，您可能希望“ data1”和“ data2”與空間分開(kāi)。

要?jiǎng)h除HTML標(biāo)籤並將其余文本與空格分開(kāi)，您可以以這種方式進(jìn)行：

1. 用空格替換標(biāo)籤“”，而不是空字符串：

   =RegExpReplace(A5, "]*>", " ")

2. 將多個(gè)空間減少到單個(gè)空間字符：

   =RegExpReplace(RegExpReplace(A5, "]*>", " "), " ", " ")

3. 修剪領(lǐng)先和落後的空間：

   =TRIM(RegExpReplace(RegExpReplace(A5, "]*>", " "), " ", " "))

結(jié)果看起來(lái)像這樣：

ablebits正則刪除工具

如果您有機(jī)會(huì)使用我們的Ultimate Suite for Excel，那麼您可能已經(jīng)發(fā)現(xiàn)了最近版本中引入的新的Regex工具。這些基於.NET的正則函數(shù)的優(yōu)點(diǎn)在於，首先，它們支持具有VBA REGEXP限制的全功能的正則表達(dá)語(yǔ)法，其次，不需要在您的工作簿中插入任何VBA代碼，因?yàn)槲覀冊(cè)卺岫说奈覀兺瓿闪怂写a集成。

您的工作部分是構(gòu)建正則表達(dá)式並將其提供給功能:)讓我向您展示如何在一個(gè)實(shí)際示例中做到這一點(diǎn)。

如何使用正則括號(hào)和括號(hào)中的括號(hào)和括號(hào)中的文字

在長(zhǎng)文本字符串中，通常將較少重要的信息包含在[括號(hào)]和（括號(hào)）中。您如何刪除保留所有其他數(shù)據(jù)的無(wú)關(guān)緊要的細(xì)節(jié)？

實(shí)際上，我們已經(jīng)構(gòu)建了一個(gè)類(lèi)似的正則延期，用於刪除HTML標(biāo)籤，即在角度括號(hào)內(nèi)的文本。顯然，相同的方法也適用於正方形和圓括號(hào)。

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

訣竅是使用懶惰量詞（*？）匹配最短可能的子字符串。第一組（\（。*？\））匹配從開(kāi)口括號(hào)到第一個(gè)閉合括號(hào)。第二組（\ [。*？\]）匹配從開(kāi)放式支架到第一個(gè)關(guān)閉括號(hào)。垂直條|充當(dāng)或操作員。

通過(guò)確定模式，讓我們將其“饋送”到我們的正則刪除功能中。以下是：

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

   

2. 在Regex Tools Pane上，選擇您的源字符串，輸入您的正則撥號(hào)，選擇“刪除”選項(xiàng)，然後命中刪除。

   要將結(jié)果作為公式，而不是值，請(qǐng)選擇插入作為公式複選框。

   要從A2：A5中的字符串中刪除括號(hào)中的文本，我們將設(shè)置配置如下：

   

結(jié)果，在您的原始數(shù)據(jù)旁邊的新列中插入了Ablebitsregexremove功能。

該函數(shù)也可以通過(guò)標(biāo)準(zhǔn)插入功能對(duì)話框直接輸入在單元格中，該函數(shù)在ablebitsudfs下對(duì)其進(jìn)行分類(lèi)。

由於AbleBitsRegeXremove旨在刪除文本，因此僅需要兩個(gè)參數(shù) - 源字符串和正則是正則。這兩個(gè)參數(shù)都可以直接以公式定義，也可以以單元參考的形式提供。如果需要，可以將此自定義功能與任何本地功能一起使用。

例如，要修剪所得字符串中的額外空間，您可以將裝飾函數(shù)用作包裝器：

=TRIM(AblebitsRegexRemove(A5, $A$2))

這就是如何使用正則表達(dá)式刪除Excel中的字符串的方法。我感謝您閱讀，並期待下週在我們的博客上與您見(jiàn)面！

可用下載

使用REGEX刪除字符串 - 示例（.xlsm File）Ultimate Suite-試用版（.EXE文件）

以上是以刪除Excel中的某些字符或文字的正則的詳細(xì)內(nèi)容。更多資訊請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！