目錄
ToggleExcel 字串擷取數字完整教學
在日常辦公與專案管理中,經常會遇到需要從混合文字與數字的字串中,精確擷取出數字的情境。例如:從「訂單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等自動化平台進行更高階的資料處理與流程優化。選擇最適合自身需求的方法,能大幅提升工作效率與資料精準度。