Excel 字串擷取數字完整教學
在日常辦公與專案管理中，經常會遇到需要從混合文字與數字的字串中，精確擷取出數字的情境。例如：從「訂單A1234」中取出「1234」、從「台北市中山區100號」中取得「100」。這類需求在資料清理、報表彙整、批次處理等場合極為常見。本文將針對不同Excel版本，詳細解析各種擷取數字的方法，並結合實際案例、錯誤處理與進階應用，協助你解決工作上的實際痛點。
常見應用場景與需求
- 訂單編號處理：如「ORD-2023-001」需擷取「2023」或「001」作為統計依據。
- 地址或備註資料清理：如「新北市板橋區56巷8號」需提取「56」與「8」作為地址欄位。
- 產品序號分解：如「SNX-AB12-3456」需擷取「12」與「3456」進行分類。
- 批次資料轉換：大量資料欄位需自動化擷取數字，減少人工處理時間。
- 報表自動化：將混合字串中的數字提取後進行加總、排序或比對。
這些需求在專案管理、銷售分析、資料庫維護等領域皆極為常見。
使用Excel內建函數擷取數字
傳統公式組合（MID、ROW、ISNUMBER等）
實例說明
假設A1儲存格內容為「客戶編號A123B456」，目標是擷取出「123456」。
步驟一：建立陣列公式
=TEXTJOIN("", TRUE, IF(ISNUMBER(VALUE(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1))), MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1), ""))
公式拆解說明：
– MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)：將A1內每個字元單獨拆出，形成一個字元陣列。
– VALUE(...)：將字元轉為數值，若非數字則產生錯誤。
– ISNUMBER(...)：判斷該字元是否為數字。
– IF(...)：若為數字則保留，否則空白。
– TEXTJOIN("", TRUE, ...)：將所有數字字元串接成一個完整數字字串。
注意事項：
– 在舊版Excel（如2016、2019）需以Ctrl+Shift+Enter輸入，成為陣列公式。
– Excel 365/2021可直接輸入，支援動態陣列。
常見錯誤與解決
- 字串無數字：公式結果為空字串，可用
IF包覆顯示提示。 - 數字分散多處：此方法會將所有數字連接成一串，若只需第一組數字，請參考進階應用。
Excel 365/2021新函數應用
TEXTBEFORE、TEXTAFTER、FILTERXML等
案例一：擷取第一組數字
假設A1內容為「產品編號：AB-1234-XY5678」。
方法一：利用FILTERXML與TEXTJOIN
=TEXTJOIN("", TRUE, FILTERXML("<t><s>"&SUBSTITUTE(A1,"","</s><s>")&"</s></t>","//s[number(.)=number(.)]"))
說明：
– SUBSTITUTE(A1,"","</s><s>")：將每個字元分隔。
– FILTERXML(...)：過濾出數字字元。
– TEXTJOIN(...)：串接數字。
方法二：利用TEXTAFTER與TEXTBEFORE（適合有明確分隔符號時）
若字串格式為「SN-1234-ABC」，要取「1234」：
=TEXTBEFORE(TEXTAFTER(A1, "-"), "-")
適用情境：
– 字串有明確分隔符號（如-、_、空格等）。
– 只需擷取特定位置的數字。
函數適用限制
- FILTERXML僅適用於Windows版Excel，且不支援部分特殊字元。
- TEXTAFTER、TEXTBEFORE僅Excel 365/2021支援。
- 若字串結構複雜，建議用傳統公式或VBA。
常見錯誤與解決方法
- FILTERXML出現#VALUE!：字串含有XML不支援的特殊字元，需先用SUBSTITUTE移除。
- TEXTAFTER找不到分隔符：會回傳錯誤，可用IFERROR包覆。
- 公式結果為空：確認字串中確有數字，或調整公式邏輯。
使用VBA自訂函數擷取數字
VBA自訂函數範例與說明
若需批次處理大量資料，或字串結構極為複雜，VBA自訂函數是高效解決方案。
VBA範例：
Function ExtractNumbers(str As String) As String
Dim i As Integer
Dim result As String
result = ""
For i = 1 To Len(str)
If Mid(str, i, 1) Like "[0-9]" Then
result = result & Mid(str, i, 1)
End If
Next i
ExtractNumbers = result
End Function
操作步驟：
1. 按下Alt + F11開啟VBA編輯器。
2. 插入新模組，貼上上述程式碼。
3. 回到工作表，輸入=ExtractNumbers(A1)即可擷取A1字串中的所有數字。
程式碼說明：
– 逐字檢查字串，遇到數字則累加至結果字串。
– 回傳所有數字連接而成的新字串。
安全性提醒：
– 請僅於信任的Excel檔案執行VBA，避免開啟來路不明的巨集。
– VBA需啟用巨集功能，部分企業環境可能有限制。
VBA與內建函數效率比較
| 方法 | 適用情境 | 優點 | 缺點 |
|---|---|---|---|
| 內建公式 | 少量資料、結構單純 | 無需程式，易於維護 | 複雜結構難處理，效率較低 |
| VBA自訂函數 | 大量資料、結構複雜、需批次處理 | 高度自動化、彈性大 | 需啟用巨集，有安全疑慮 |
進階應用與常見問題（FAQ）
如何只取第一組數字？
若字串為「A12B34C56」，僅需「12」：
公式：
=TEXTJOIN("", TRUE, IFERROR(MID(A1, MATCH(TRUE, ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)), 0), ROW(INDIRECT("1:"&LEN(A1)))), IF(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)), MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1), ""), ""))
（複雜公式，建議分步拆解或用VBA）
如何處理負數或小數？
- 傳統公式難以直接判斷負號或小數點，建議用VBA進行進階判斷。
- VBA可修正為：
If Mid(str, i, 1) Like "[0-9.-]" Then
並加上邏輯判斷，確保負號與小數點僅出現在合理位置。
如何批次處理多儲存格？
- 內建公式可拖曳填滿多格。
- VBA可設計為批次處理整欄資料，或配合自動化工具如Monday.com進行大規模資料清理。
字串中沒有數字會怎樣？
- 內建公式結果為空字串。
- 可用
IF(公式="","無數字",公式)提示。
公式結果出現錯誤怎麼辦？
- 檢查字串是否含特殊符號、空白或格式異常。
- 使用
IFERROR包覆公式，避免錯誤訊息干擾。
總結與工具推薦
從傳統公式到Excel 365新函數，再到VBA自訂函數，Excel字串擷取數字的方法多元且彈性高。對於結構單純、資料量不大者，建議優先使用內建公式或新函數，操作簡便且易於維護；若需批次處理大量或複雜資料，則可考慮VBA自訂函數，或結合Monday.com、ClickUp等自動化平台進行更高階的資料處理與流程優化。選擇最適合自身需求的方法，能大幅提升工作效率與資料精準度。
