提取數字的函數公式：掌握Excel、WPS等表格軟體中純數字提取的技巧與應用

在日常數據處理中，我們經常會遇到需要從一串包含文本和數字的字元串中精確提取出數字部分的需求。無論是為了後續的數值計算、數據分析，還是單純的數據清洗，掌握高效的提取數字的函數公式至關重要。本文將深入探討在Excel、WPS等主流表格軟體中，如何利用各種函數公式靈活、準確地提取數字，從基礎方法到高級技巧，助您輕鬆駕馭數據。

面對混雜著文本和數字的單元格內容，手動提取數字不僅效率低下，還極易出錯。幸運的是，強大的函數公式為我們提供了自動化、精確的解決方案。本篇文章將詳細講解多種提取數字的函數公式，並提供具體的示例。

基礎數字提取方法：針對特定模式

對於一些結構相對簡單的字元串，我們可以利用基本的文本函數進行提取。

1. 提取開頭或結尾的純數字串

如果您的數字總是位於字元串的開頭或結尾，並且長度固定或可預測，可以使用LEFT和RIGHT函數。

示例：提取字元串開頭的純數字

假設A1單元格內容為「12345ABCDE」，您想提取「12345」。

=VALUE(LEFT(A1,5))

LEFT(A1,5)：從A1單元格的左側提取5個字元，得到「12345」。
VALUE()：將提取出的「12345」（此時為文本格式）轉換為數字格式，以便後續計算。

如果數字的長度不固定，但其後總跟著第一個非數字字元，我們可以結合MIN、FIND和一系列數字字元進行查找。

=VALUE(LEFT(A1,MIN(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),LEN(A1)+1,ROW(INDIRECT("1:"&LEN(A1))))) - 1))

這是一個數組公式（輸入后需按Ctrl+Shift+Enter確認），它通過判斷每個字元是否為數字，來確定數字串的結束位置。

示例：提取字元串結尾的純數字

假設A1單元格內容為「ABCDE12345」，您想提取「12345」。

=VALUE(RIGHT(A1,5))

RIGHT(A1,5)：從A1單元格的右側提取5個字元，得到「12345」。
VALUE()：同上，將提取出的文本數字轉換為實際數字。

2. 從混合文本中提取首個連續數字串（通用方法）

當數字嵌在字元串中間時，我們需要更複雜的邏輯來定位數字的起始和結束位置。

方法一：利用MID、ROW和INDIRECT函數組合（數組公式）

此方法可以提取字元串中找到的第一個連續數字序列。

假設A1單元格內容為「訂單號OD1234567890生效」，您想提取「1234567890」。

=VALUE(MID(A1,MIN(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))),LEN(A1)+1)),SUMPRODUCT(N(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))

這是一個數組公式，輸入后務必按Ctrl+Shift+Enter確認。

ROW(INDIRECT("1:"&LEN(A1)))：生成一個從1到字元串長度的數字序列，用於遍歷每個字元。
MID(A1,ROW(...),1)：逐個提取字元串中的字元。
ISNUMBER(--...)：判斷提取出的單個字元是否為數字。--（雙負號）將文本數字轉換為數字，如果不是數字則產生錯誤，ISNUMBER據此返回TRUE/FALSE。
MIN(IF(ISNUMBER(...),ROW(...),LEN(A1)+1))：找到第一個數字字元的起始位置。
SUMPRODUCT(N(ISNUMBER(--...)))：計算字元串中所有數字字元的總個數（即數字串的長度）。
MID(A1,起始位置,長度)：根據找到的起始位置和長度提取數字串。
VALUE()：將結果轉換為數字。

方法二：通過替換非數字字元來提取（適用於所有數字）

此方法適用於您希望將字元串中所有的數字字元拼接起來形成一個數字，忽略中間的非數字字元。

假設A1單元格內容為「我的身高175cm，體重68.5kg」，您想提取「175685」。

=VALUE(TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"")))

這同樣是一個數組公式，輸入后按Ctrl+Shift+Enter確認。

ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))：逐個字元判斷是否為數字。
IF(...)：如果是數字，則保留該字元；否則，返回空字元串。
TEXTJOIN("",TRUE,...)：將所有保留的數字字元連接起來。第一個參數""表示連接符為空，第二個參數TRUE表示忽略空值。
VALUE()：將結果轉換為數字。

注意： TEXTJOIN函數在Excel 2019、Microsoft 365和WPS等較新版本中可用。對於早期Excel版本（如Excel 2016及更早版本），需要使用更複雜的數組公式或VBA宏。

高級技巧與多數字提取

1. 使用FILTERXML函數（Excel 2013+，僅限Windows）

FILTERXML是一個強大的解析函數，尤其適合從包含分隔符的文本中提取特定類型的數據。它需要將字元串轉換為XML格式。

假設A1單元格內容為「產品編號：12345，數量：67，價格：88.99」，您想提取所有數字。

=FILTERXML(""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"，",""),"：","")," ","")&"","//n[number()=number()]")

此公式會返回一個包含所有識別為數字的數組。如果只想要第一個，可以結合INDEX函數。

SUBSTITUTE(A1,"，","")等：將文本中的逗號、冒號、空格等分隔符替換為XML的節點標籤。
""&...&""：構建一個完整的XML字元串，使其成為有效的XML結構。
"//n[number()=number()]"：這是一個XPath表達式，它查找所有名為的節點，並篩選出那些其內容可以被解析為數字的節點。number()=number()是一個判斷是否為數字的巧妙技巧。

局限性： FILTERXML函數主要在Windows版的Excel 2013及更高版本中可用，且WPS和Mac版Excel可能不支持此功能。此外，它依賴於XML解析，如果原始字元串中包含特殊XML字元，可能需要額外的處理。

2. 提取多個不連續的數字串（進階，需要輔助列或VBA）

如果您的目標是提取字元串中所有獨立且不連續的數字串（例如「數量12，價格34，重量56」中的12、34、56），僅用一個單元格公式會非常複雜且效率低下。通常有以下幾種方式：

輔助列結合查找與截取： 在多個輔助列中，通過迭代查找下一個數字的起始位置和結束位置來逐個提取。這種方法雖然複雜，但在早期Excel版本中是純公式的常見選擇。
宏/VBA編程： 編寫VBA函數是處理此類複雜文本解析最靈活和強大的方法。您可以創建自定義函數，輕鬆實現複雜的數字提取邏輯。
Power Query（數據轉換工具）： 對於批量數據處理，Excel和WPS中的Power Query（在「數據」選項卡下的「獲取和轉換數據」）提供了強大的圖形化界面來提取和轉換數據，無需編寫複雜公式。可以通過「按分隔符拆分列」或「提取數字」等功能實現。

提取后的數據類型轉換與常見問題

1. 確保數字格式：VALUE函數與強制轉換

使用LEFT、RIGHT、MID等文本函數提取出來的「數字」實際上是文本格式的。這意味著它們不能直接用於數學計算（如求和、平均值等）。您需要將它們轉換為真正的數字。

VALUE()函數： 最直接的方法，如=VALUE("123")將返回數字123。
算術運算： 將文本數字與一個數字進行算術運算（如乘以1、除以1、加0、減0）也能強制轉換為數字。

="123"*1 或 ="123"+0
雙負號（--）： 這是一個常用的簡潔方式，它將布爾值或文本數字轉換為數字。

=--"123"

2. 錯誤處理：IFERROR函數

當字元串中不包含數字，或公式邏輯無法找到有效數字時，上述某些公式可能會返回#VALUE!、#NUM!等錯誤。為了使表格更整潔，可以使用IFERROR函數進行錯誤處理。

=IFERROR(您的提取數字公式,"")

如果「您的提取數字公式」返回錯誤，則顯示空字元串""；否則，顯示提取出的數字。您也可以將其替換為0或其他任何您希望在無數字時顯示的值。

實際應用場景

掌握提取數字的函數公式在多種場景下都非常實用：

數據清洗與標準化： 從非結構化文本（如產品描述、用戶評論）中提取出關鍵的數值信息，以便進行分類、統計和分析。
產品編碼或批次號提取： 從包含字母、符號的產品編碼中，快速識別並提取出數字部分的批次號或序列號。
地址信息解析： 從詳細地址字元串中，提取出門牌號、樓層等數字信息。
財務報表處理： 從格式不統一的財務文本中提取金額、百分比等數值，便於後續的計算和匯總。
日誌文件分析： 從伺服器日誌或系統日誌中，提取時間戳、錯誤代碼、數據量等數字指標。

常見問題解答（FAQ）

如何判斷提取出的數字是否為文本格式？

您可以使用ISNUMBER()函數來判斷單元格內容是否為數字格式。例如，如果A1單元格中是文本格式的數字「123」，=ISNUMBER(A1)將返回FALSE；如果它是數字格式的123，則返回TRUE。此外，您也可以檢查單元格默認的對齊方式，數字通常右對齊，文本通常左對齊（但這不是絕對的判斷標準）。

為何我的數組公式輸入后不生效？

如果數組公式（例如本文中使用了ROW(INDIRECT(...))和IF結合的公式）沒有按預期工作，很可能是您忘記在輸入公式后按Ctrl+Shift+Enter（而不是只按Enter鍵）來確認。當正確輸入數組公式后，公式欄中的公式會自動被大括弧{}包圍，例如{=VALUE(...)}。

如何提取包含小數或負數的數字？

本文中提及的VALUE()函數以及強制類型轉換（如乘以1、雙負號--）通常能夠正確處理包含小數點的數字和負號。例如，從「價格-12.34元」中提取「-12.34」，只要確保提取到的子字元串是完整的數字格式（包括負號和小數點），VALUE()函數就能正確轉換。FILTERXML函數也能很好地處理這類情況。

如果字元串中沒有數字，公式會返回什麼？

如果字元串中不包含任何數字，或者不符合公式所設定的提取模式，大部分提取數字的公式會返回錯誤，例如#VALUE!或#NUM!。為了避免顯示這些錯誤，您可以使用IFERROR函數進行包裹，如=IFERROR(您的提取數字公式, "")，這樣在沒有數字時會顯示空白或您指定的其他值。

有沒有更簡單的方法，不需要那麼複雜的公式？

當然有！對於某些簡單或批量操作，Excel/WPS提供了更直觀的工具：

「快速填充」（Flash Fill）： 這是Excel 2013及更高版本提供的智能功能。在旁邊列輸入幾個示例，Excel會自動識別模式並填充剩餘部分。這對於提取數字非常方便，無需公式。
「分列」（Text to Columns）： 如果數字與文本之間有固定的分隔符（如空格、逗號），可以使用「數據」選項卡下的「分列」功能，選擇分隔符或固定寬度來將數據拆分開。
Power Query： 對於更複雜的數據清洗和轉換任務，Power Query（數據選項卡 -> 獲取和轉換數據）提供了強大的圖形界面和M語言，可以非常靈活地提取和處理數據，通常比純公式更易維護和擴展。

本文主要側重於提取數字的函數公式，以提供無需額外工具即可在單元格內完成操作的解決方案。

總結

掌握各種提取數字的函數公式是數據處理中的一項核心技能。從簡單的LEFT/RIGHT到複雜的數組公式，再到現代的TEXTJOIN和FILTERXML，每種方法都有其適用場景和優劣。熟練運用這些公式，不僅能大大提高您的工作效率，還能讓您在面對複雜、非結構化數據時遊刃有餘。建議您結合實際數據情況，多加練習，找到最適合您的解決方案。