如何在Excel中使用正則函數

Excel 正則表達式函數速覽：REGEXTEST、REGEXEXTRACT 和 REGEXREPLACE

正則表達式 (REGEX) 是用於檢查文本字符串是否符合給定模式以及提取或替換與給定模式匹配的文本字符串的搜索模式。鑑於其複雜性，本文提供其在 Excel 中使用的簡化摘要和示例。

REGEX 函數適用於使用 Windows 或 Mac 版 Microsoft 365 Excel 的用戶，以及使用網絡版 Excel 的用戶。

REGEXTEST：文本模式匹配測試

此函數測試文本字符串是否與給定模式匹配，根據測試結果返回 TRUE 或 FALSE。這是測試數據是否遵循特定模式的好方法。

語法

<code>REGEXTEST(a, b, c)</code>

其中：

• a (必需) 是要測試的文本、值或包含文本的單元格引用。
• b (必需) 是用於執行測試的模式。
• c (可選) 如果希望測試區分大小寫，則為 0；否則為 1。

REGEXTEST 使用示例

此電子表格包含必須遵循嚴格結構的產品代碼列表。

有效代碼包含：

• 產品尺寸的小寫表示形式（“xs”表示特小，“s”表示小，“m”表示中等等），
• 表示產品材料的一位或兩位數字，
• 表示產品製造地點的三個大寫字母，以及
• 三個部分之間用短橫線分隔。

我們需要測試所有產品代碼是否都匹配此結構。

因此，在單元格 B2 中，輸入以下公式：

<code>=REGEXTEST([@Code],"[xs|s|m|l|xl]-[0-9]{1,2}-[A-Z]{3}",0)</code>

其中：

• [@Code] 是包含要測試的代碼的列的結構化引用。
• [xs|s|m|l|xl] 是要測試的產品代碼的第一部分，豎線表示“或”。
• [0-9]{1,2} 是要測試的產品代碼的第二部分，[0-9] 表示任何一位數字， {1,2} 表示可以有一位或兩位數字。
• [A-Z]{3} 是要測試的產品代碼的第三部分，[A-Z] 表示任何大寫字母， {3} 表示必須正好有三個大寫字母。
• 要測試的代碼的三個部分用短橫線分隔。
• 0 是公式中的最後一個參數，它告訴 Excel 測試區分大小寫。

按 Enter 鍵將此公式應用於 B 列中的所有行後，結果顯示只有兩個代碼有效 (TRUE)。

• m-2-UK 無效（由 FALSE 結果指示），因為國家代碼只包含兩個大寫字母。
• xl-714-AUS 無效，因為材料代碼包含三位數字。
• S-5-USA 無效，因為尺寸代碼是大寫的。

此示例包含 [] 和 {} 等字符的使用。但是，還有許多其他字符（也稱為標記）也可用於確定用於執行測試的模式，其中一些將在下面的示例中使用。

REGEXEXTRACT：提取特定文本片段

此函數根據指定的模式返回單元格中部分文本。例如，您可能希望分離數字和文本。

語法

<code>REGEXTEST(a, b, c)</code>

其中：

• d (必需) 是要從中提取文本的文本、值或單元格引用。
• e (必需) 是要提取的模式。
• f (可選) 如果只想提取第一個匹配項，則為0；如果要將所有適用的匹配項作為數組提取，則為1；如果要從第一個匹配項提取組，則為2。
• g (可選) 如果希望提取區分大小寫，則為 0；否則為 1。

由於格式化的 Excel 表格無法處理溢出數組，如果您打算在參數 f 中將匹配項作為數組提取，請確保您的數據是普通格式的。

REGEXEXTRACT 使用示例

在此示例中，需要將客戶的姓名和電話號碼提取到三個單獨的列中。

首先關注姓名。在單元格 B2 中，輸入以下公式：

<code>=REGEXTEST([@Code],"[xs|s|m|l|xl]-[0-9]{1,2}-[A-Z]{3}",0)</code>

其中：

• A2 是包含要提取數據的單元格。
• [A-Z][a-z] 告訴 Excel 要提取任何以大寫字母開頭後跟小寫字母的單詞，其中 " " 表示要返回每個模式中一個或多個小寫字母。
• 1 表示希望將上述模式的每個示例作為數組分離到各個單元格中（換句話說，第一個姓名在單元格 B2 中，第二個姓名在單元格 C2 中）。如果省略此參數，Excel 將只在單元格 B2 中返回第一個匹配項（第一個姓名）。

按 Enter 鍵後，Excel 將成功執行提取並將淡藍色線添加到單元格 C2 周圍，以提醒您它是一個溢出數組。

選擇單元格 B2 後，現在可以使用單元格右下角的填充柄將此相對公式複製到其餘的詳細信息行。

現在，需要使用類似的 REGEXTRACT 公式來提取客戶的電話號碼。在單元格 D2 中，輸入以下公式：

<code>REGEXEXTRACT(d, e, f, g)</code>

其中：

• A2 是包含要提取數據的單元格。
• [0-9()] 從零到九的數字提取在圓括號內的數字，其中 " " 提取此模式中的一個或多個數字。
• [0-9-] 提取字符串中剩餘的數字，第二個"-" 表示分隔電話號碼的兩個部分的短橫線，" " 告訴Excel 如果字符串包含數字，則要提取一個或多個數字。

由於 A 列中每個單元格中此模式只有一個實例，因此不需要添加任何其他參數。同樣，一旦檢查了此公式是否產生預期結果，就可以使用填充柄將其複製到 D 列中的其餘單元格。

Excel 中還有其他方法可以提取數據並獲得類似的結果，例如使用 TEXTSPLIT 函數或 Excel 的快速填充工具。

REGEXREPLACE：操作數據

此函數獲取單元格中的文本並在另一個單元格中創建該數據的新版本。即使函數稱為 REGEXREPLACE，它實際上並沒有替換其原始位置中的原始文本。

語法

<code>REGEXTEST(a, b, c)</code>

其中：

• h (必需) 是要替換的文本、值或包含文本的單元格引用。
• i (必需) 是要替換的模式。
• j (必需) 是要創建的替換內容。
• k (可選) 是要替換的模式的出現次數。
• l (可選) 如果希望替換區分大小寫，則為 0；否則為 1。

REGEXREPLACE 使用示例

下面，可以看到 A 列中的一系列姓名。目標是在 B 列中重新創建這些姓名，但使用“姓氏，名字”格式，包括分隔姓名的逗號。

在單元格 B2 中，輸入以下公式：

<code>=REGEXTEST([@Code],"[xs|s|m|l|xl]-[0-9]{1,2}-[A-Z]{3}",0)</code>

其中：

• [@Client name] 引用包含要影響的數據的列。
• [A-Z][a-z] 在公式中包含兩次（並用空格分隔），告訴 Excel 要獲取包含大寫字母后跟一個或多個小寫字母的兩個文本字符串。
• , 告訴 Excel 要反轉這兩個文本字符串的順序，並用逗號和空格分隔。如果不包含美元符號，Excel 將只返回“2, 1”作為每個單元格的結果。

沒有在上面的公式中處理參數k 和l，因為希望Excel 替換所有出現（參數k 的默認值），並且希望替換區分大小寫（參數l 的默認值）。

因為使用的是格式化表格，所以按 Enter 鍵後，公式將應用於 B 列中的其餘單元格。

---

正則表達式不僅可用於 Excel。實際上，可以使用 REGEX 自動執行計算機上的其他任務，例如修復複製粘貼的 PDF 文本、批量重命名下載的文件、格式化貨幣、去除 HTML 標記等等。

以上是如何在Excel中使用正則函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！