如何使用 Excel 的正規表示式函數增強搜尋功能

在 Excel 中篩選和搜尋並不是一項簡單的任務。正規表示式函數將會改變這一點。現在,您可以毫不費力地準確指定您需要的內容 - 複雜模式、部分匹配或結構化資料提取。

目錄

什麼是正規表示式?

正規表示式是一種用於搜尋文字或字串以查找匹配項的模式。您是否想過網站如何告訴您在登入頁面上輸入的電子郵件範本無效?以下是使用電子郵件簽名的正規表示式模式的範例。

正規表示式並不是 Excel 獨有的——它們存在於許多文字編輯器、程式語言、命令列工具、IDE,甚至 Excel 的競爭對手Google Sheets 中

正規表示式可能看起來很複雜,如果你想充分利用它的話它確實很複雜,但你不需要成為一名程式設計師就可以有效地使用它。在某些情況下,您只需知道如何使用一些基本符號和模式就可以了。本指南將盡可能簡單,以便您可以開始使用它們。

以下是本指南中將使用的符號:

象徵

描述

-

在括號中指定字元的範圍。

^

匹配字串的開頭。

$

匹配字串的結尾。

匹配換行符之外的任意字元。

*

匹配零個或多個前面的字元。

+

匹配前一個字元或多個字元。

()

將匹配的字元組合成一個。

[]

匹配括號內的任意字元。

[^]

匹配括號內以外的任何字元。

{n}

精確匹配前一個字元的 n 個實例。

{n,}

匹配前一個字元的 n 次或更多次出現

您可以使用這些符號建立的簡單正規表示式模式包括:

正規表示式模式

描述

[0-9]

匹配 0 到 9 之間的數字

[a-zA-z0-9]

這是匹配範圍,匹配從小寫字母 a 到 z、大寫字母 A 到 Z 和 0 到 9 的單個字元。

^pro

匹配以pro開頭的任何字串。

[^$]

匹配$之外的任意字元。

(孩子)

子組樣本。

一{3,}

匹配a後面的部分的 3 個或更多個出現(例如aaa 或aaa)。

正規表示式函數是預先定義的 Excel 公式,可用於定義搜尋和操作文字字串的模式。目前有 3 個正規表示式函數。我們將了解如何單獨使用它們以及如何與其他功能一起使用它們。

搜尋模式

我們將要研究的第一個函數是 REGEXTEST。此函數採用您想要用於搜尋的文字字串和正規表示式模式,然後使用後一種模式在前一種模式中尋找匹配項。該函數將傳回 True 或 False。

REGEXTEST 函數的語法如下:

REGEXTEST(string_to_search, regex_pattern_to_use, [case_senstivity])

前兩個參數string_to_searchregex_pattern_to_use 的意思非常明顯。[case_sensitivity]參數是可選的 - 在談論 Excel 語法時,方括號中的任何內容都是可選的 - 並指示您是否希望搜尋區分大小寫(0)或不區分大小寫(1)。預設區分大小寫。

此範例將使用 REGEXTEST 透過以下公式查看使用者是否輸入了有效的電子郵件地址:

REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$")

在這裡,我們在儲存格 B3 中搜索,查看它是否包含電子郵件地址,使用以下正規表示式模式:

^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$

如果您將公式放在儲存格 C3 中,並在儲存格 B3 中輸入[email protected],則公式將傳回 True,因為它與電子郵件簽章相符。

如何使用 Excel 的正規表示式函數增強搜尋功能
Excel 中的 REGEXTEST 函數。

使用正規表示式的附加數據

接下來我們來看看REXEXEXTRACT函數。此函數傳回與提供的正規表示式模式相符的子字串(字串的一部分)。

REXEXEXTRACT 函數的語法如下:

REGEXEXTRACT(string_to_search, regex_pattern_to_use, [return_mode], [case_senstivity])

繼續電子郵件範例,讓我們為儲存格 B4 新增一個公式來提取電子郵件部分的使用者名稱。

公式如下:

=REGEXEXTRACT(B3, "([^@]+)")

在這個公式中,我們提取在 B3 中輸入的電子郵件地址中@符號之前的所有內容。

如何使用 Excel 的正規表示式函數增強搜尋功能
Excel 中的 REGEXEXTRACT 函數。

使用正規表示式尋找和替換

我們將要研究的最後一個正規表示式函數是 REGEXREPLACE。函數類似Excel的REPLACE函數,但也支援RegEx。它會取得您想要修改的文字字串並檢查是否有任何子字串與定義的正規表示式模式相符。如果找到,它會用提供的替換字串替換該字串。

REGEXREPLACE 函數的語法如下:

REGEXREPLACE(string_to_modify, regex_pattern_to_use, replacement_string, [number_of_occurrences], [case_senstivity])

以下是該函數中需要注意的重要參數:

  • string_to_modify:要修改的文字字串。
  • replacement_string:用於替換子字串的字串。
  • number_of_occurrences:您想要替換的確切實例。

以下是使用該函數將電子郵件的使用者名稱部分替換為另一個文字字串的範例:

=REGEXREPLACE(B3, "^[^@]+", "jane.doe")

B3 的值為[email protected],在儲存格 C3 中輸入上述公式後,它將傳回[email protected]

如何使用 Excel 的正規表示式函數增強搜尋功能
Excel 中的 REGEXREPLACE 函數。

將正規表示式與其他函數結合

您也可以將正規表示式函數與 Excel 中的其他函數結合使用。例如,您可以將 REGEXTEST 函數與 Excel 的 IF 語句結合起來,並根據結果顯示適當的訊息。

以下是一個範例公式:

=IF(REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"), "This is a valid email address!", "The email address is invalid!")

此公式使用 IF 語句檢查儲存格 B6 中輸入的電子郵件地址是否有效,然後顯示這是一個有效的電子郵件地址!如果為TRUE電子郵件地址無效!如果為FALSE。此外,您可以將此公式與FIND 函數配對,以便在 Excel 中快速找到資料。

如何使用 Excel 的正規表示式函數增強搜尋功能
在 Excel 中結合 REGEXTEST 函數和 IF 函數。

這是在 Excel 中開始使用 RegEx 的好方法。用例和可能性僅受您的想像力的限制。

Sign up and earn $1000 a day ⋙

Leave a Comment

普通電視和智慧電視的差別

普通電視和智慧電視的差別

智慧電視確實風靡全球。憑藉如此多的強大功能和互聯網連接,科技改變了我們觀看電視的方式。

為什麼冷凍室沒有燈,冷藏室有燈?

為什麼冷凍室沒有燈,冷藏室有燈?

冰箱是家庭中常見的家用電器。冰箱通常有 2 個隔間,冷藏室寬敞,並且每次使用者打開時都會自動亮燈,而冷凍室狹窄且沒有燈。

解決 Wi-Fi 速度變慢的網路擁塞問題的 2 種方法

解決 Wi-Fi 速度變慢的網路擁塞問題的 2 種方法

除了路由器、頻寬和乾擾之外,Wi-Fi 網路還受到許多因素的影響,但也有一些智慧的方法可以增強您的網路效能。

如何使用 Tenorshare Reiboot 將 iOS 17 降級到 iOS 16 且不遺失數據

如何使用 Tenorshare Reiboot 將 iOS 17 降級到 iOS 16 且不遺失數據

如果您想在手機上恢復穩定的 iOS 16,這裡是卸載 iOS 17 並從 iOS 17 降級到 16 的基本指南。

每天吃優​​格會對身體產生什麼影響?

每天吃優​​格會對身體產生什麼影響?

酸奶是一種美妙的食物。每天吃優​​格好嗎?每天吃酸奶,你的身體會發生怎樣的變化?讓我們一起來了解一下吧!

哪種米最有益於健康?

哪種米最有益於健康?

本文討論了最有營養的米種類以及如何最大限度地發揮所選米的健康益處。

如何早上準時起床

如何早上準時起床

制定睡眠時間表和就寢習慣、更改鬧鐘以及調整飲食都是可以幫助您睡得更好、早上準時起床的一些措施。

玩 Rent Please! 的提示新手模擬房東

玩 Rent Please! 的提示新手模擬房東

請租用! Landlord Sim 是一款適用於 iOS 和 Android 的模擬手機遊戲。您將扮演一個公寓大樓的房東,開始出租公寓,目標是升級公寓的內部並為租戶做好準備。

最新浴室塔防代碼及代碼輸入方法

最新浴室塔防代碼及代碼輸入方法

取得浴室塔防 Roblox 遊戲代碼並兌換令人興奮的獎勵。它們將幫助您升級或解鎖具有更高傷害的塔。

變壓器的結構、符號和工作原理

變壓器的結構、符號和工作原理

讓我們以最準確的方式了解變壓器的結構、符號和運作原理。

人工智慧讓智慧電視更上一層樓的四種方式

人工智慧讓智慧電視更上一層樓的四種方式

從更好的影像和聲音品質到語音控制等等,這些人工智慧功能讓智慧電視變得更好!

為什麼 ChatGPT 比 DeepSeek 更好

為什麼 ChatGPT 比 DeepSeek 更好

最初,人們對DeepSeek寄予厚望。作為 ChatGPT 強勁競爭對手的 AI 聊天機器人,它承諾提供智慧聊天功能和體驗。

認識 Fireflies.ai:免費的 AI 秘書,幫你節省大量工作時間

認識 Fireflies.ai:免費的 AI 秘書,幫你節省大量工作時間

在記下其他重要事項時很容易錯過重要細節,而在聊天時記筆記可能會分散注意力。 Fireflies.ai 就是解決方案。

如何飼養 Minecraft 中的美西蠑螈,馴服 Minecraft 中的蠑螈

如何飼養 Minecraft 中的美西蠑螈,馴服 Minecraft 中的蠑螈

如果玩家知道如何使用 Axolot Minecraft,它將成為玩家在水下操作時的絕佳助手。

《噤界:前路》PC遊戲配置

《噤界:前路》PC遊戲配置

《寂靜之地:前路》的配置評價相當高,因此您需要在決定下載之前考慮其配置。