如何在 Excel 中將電話號碼格式轉換為數字?
當您在處理包含電話號碼清單的 Excel 工作表時,可能會經常遇到各種格式樣式,例如括號、空格或破折號。這些非數字字符可能會干擾需要純數字序列的電話號碼的數據分析、系統導入或自動撥號流程。如果您需要通過僅提取格式化電話號碼中的數字來標準化您的數據,本文提供了幾種實用的解決方案,以高效地在 Excel 中實現這一目標。
以下是一個您可能面臨的情況示例——左列包含不同格式的電話號碼,而右列顯示了期望的結果:移除所有非數字字符後的電話號碼,只留下連續的數字串。
使用公式將電話號碼格式轉換為數字
使用快速填充從格式化的電話號碼中提取數字
使用 Kutools for Excel 將電話號碼格式轉換為數字
使用宏去除電話號碼中的所有非數字字符
使用公式將電話號碼格式轉換為數字
Excel 的公式允許您刪除不需要的字符,並在電話號碼列表中只保留數字。如果您不想使用外掛程式或編程,這種方法特別有用,並且對於遵循一致格式的電話號碼(如 (123)456-7890 或 123-456-7890)非常有效。然而,如果您的數據包含額外的非標準符號或字母,則可能需要更先進的技術。
1. 選擇一個空白單元格(例如 C2),在其中顯示提取的數字。輸入以下公式並按 Enter:
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")," ",""),"-","")
注意:在此公式中,A2 是包含原始電話號碼的單元格引用。如果您的數據在不同的列或行中,可以修改此引用。
2. 在仍然選擇 C2 單元格時,拖動填充柄(單元格右下角的小方塊)向下填充其餘列表的公式(例如,從 C2 到 C8)。這將對範圍內的每個電話號碼應用該公式。
現在,您數據範圍內的每個電話號碼都應該被轉換為僅包含數字的序列。
使用快速填充從格式化的電話號碼中提取數字
Excel 的快速填充功能提供了一個智能、手動的替代方案,用於從電話號碼中提取數字——特別是在處理一致的列表格式時,或者當您想要一種快速且無需公式的解決方案時效果尤佳。快速填充根據您的手動輸入評估模式,然後填充剩餘的列以匹配您的示例。
1. 假設您的電話號碼在 A 列中,從 A2 開始。在相鄰的列(B2)中,手動輸入第一個電話號碼的數字,省略任何符號或空格(例如:1234567890)。
2. 移到 B3 單元格。開始輸入下一個電話號碼的數字。當你開始輸入時,Excel 會自動檢測模式並顯示建議的自動填充(在輸入值下方以灰色突出顯示)。
3. 按 Enter 接受快速填充建議,Excel 將填滿該列,並提取所有對應行的數字。
提示:如果快速填充未自動觸發,您可以通過選擇已完成的單元格並使用快捷鍵 Ctrl + E,或點擊功能區中的 數據 > 快速填充 來手動啟用它。
預防措施:檢查填充結果的準確性,特別是如果電話號碼有不同的格式。快速填充可能無法很好地處理具有不可預測間距、混合格式或嵌入文本的列表。
總結建議:快速填充是一次性或小數據集的快速方法,但如果您的原始數據發生變化,它不會動態更新。對於不斷更新的列表,請考慮使用公式或 VBA 方法。
使用 Kutools for Excel 將電話號碼格式轉換為數字
對於尋求更方便、互動解決方案的用戶,Kutools for Excel 中的「刪除字符」工具可以有效地刪除指定的符號——包括破折號、空格、括號等——從選定的單元格中。這種方法非常適合那些經常處理結構化數據並希望有一種快速、菜單驅動的方法來清理電話號碼而不寫公式或代碼的人。
1. 選擇包含要轉換為數字的電話號碼的範圍,然後導航至 Kutools > 文本 > 刪除字符。
2. 在「刪除字符」對話框中,選擇「自訂」選項,並在文本框中輸入連字符 – 。您還可以指定要刪除的其他字符,例如空格、括號或句點,將它們添加到框中。設定完成後,點擊 OK 處理範圍。
所選的電話號碼將立即更新,刪除指定的字符,留下乾淨的數字序列,如下所示。
如果您想免費試用(30天)此工具,請點擊下載,然後按照上述步驟進行操作。
使用宏去除電話號碼中的所有非數字字符
如果您的任務涉及許多混雜或不可預測格式的電話號碼(包括特殊字符、空格、額外標點符號、文本甚至國家代碼),Excel 的 VBA 宏可以提供高度靈活且自動的方式來一次性清理這些條目。這種方法對於大型數據集或重複清理任務特別有用。
適用場景:如果您的電話號碼格式不規則,有許多行需要處理,或者公式和內置功能無法完全滿足您的需求,請使用 VBA。但是,使用宏需要將您的工作簿保存為支持宏的格式 (*.xlsm) 並授予宏權限。
步驟:
1. 按 Alt + F11 打開 Visual Basic for Applications 編輯器。在新窗口中,點擊 插入 > 模塊 以創建一個空白模塊,然後將以下 VBA 代碼複製並粘貼到代碼窗口中:
Sub StripNonDigitsFromPhoneNumbers()
Dim xRg As Range
Dim xCell As Range
Dim xDigits As String
Dim i As Integer
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set xRg = Application.Selection
Set xRg = Application.InputBox("Select the range with phone numbers", xTitleId, xRg.Address, Type:=8)
For Each xCell In xRg
xDigits = ""
For i = 1 To Len(xCell.Value)
If Mid(xCell.Value, i, 1) Like "#" Then
xDigits = xDigits & Mid(xCell.Value, i, 1)
End If
Next i
xCell.Value = xDigits
Next xCell
End Sub
2. 點擊 運行 按鈕(或按 F5)。在出現的對話框中,確認或選擇您的數據範圍。該宏將自動從所選範圍內的每個單元格中刪除所有非數字字符——只留下純數字序列。
參數解釋:此宏處理每個單元格中的每個字符,並將僅數字字符附加到結果中。在運行之前調整選取範圍以覆蓋所有所需的列/行。
提示與錯誤提醒:首次運行 VBA 腳本之前,務必保存您的工作簿,因為更改不能一鍵撤銷。如果遇到宏安全警告,請驗證代碼來源並根據需要啟用宏。對於協作環境,請謹慎操作以免覆蓋重要數據。
總結建議:運行宏後,快速掃描清理後的數據以確保準確性。如果需要國家代碼或分機號碼,請注意這些將被合併到數字字符串中;如有必要,請單獨處理這些元素。
相關文章:
最佳 Office 辦公效率工具
🤖 | Kutools AI Aide:徹底革新數據分析,基於智能執行|生成程式碼|創建自訂公式|分析數據並生成圖表|調用 Kutools Functions… |
熱門功能:查找、選取項目的背景色或標記重複值 | 刪除空行 | 合併列或單元格且不丟失資料 | 四捨五入... | |
高級 LOOKUP:多條件查找|多值查找|多表查找|模糊查找... | |
高級下拉列表:快速創建下拉列表 | 關聯下拉列表 | 多選下拉列表 ... | |
列管理器: 添加指定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 區域與列比較 ... | |
精選功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿及工作表管理器 | 資源庫 (自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按清單發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/傾斜/刪除線 ...)... | |
前15 大工具集:12 款文本工具(添加文本,刪除特定字符,...)|50+ 種圖表 類型(甘特圖,...)|40+ 實用 公式(基於生日計算年齡,...)|19 款插入工具(插入QR码,按路徑插入圖片,...)|12 款轉換工具(金額轉大寫,匯率轉換,...)|7 款合併和分割工具(高級合併行,分割儲存格,...)| ...以及更多 |
利用 Kutools for Excel 大幅提升你的 Excel 技能,感受前所未有的高效體驗。 Kutools for Excel 提供超過300 項高級功能,助你提升效率並保存時間。 點此查看你最需要的功能...
Office Tab 為 Office 帶來標籤式介面,讓你的工作更加輕鬆
- 啟用 Word、Excel、PowerPoint 的標籤式編輯和閱讀功能
- 在同一個視窗的標籤中打開和創建多個文件,而不是在新窗口中分開開啟。
- 可提升你50% 的工作效率,每天為你大量減少滑鼠點擊次數!