如何在 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. 在「刪除字符」對話框中,選擇「自定義」選項,並在文本框中輸入破折號 –。您還可以指定要刪除的其他字符,例如空格、括號或句點,將它們添加到文本框中。設置完成後,點擊「確定」以處理範圍。
所選的電話號碼將立即更新,刪除指定的字符,留下乾淨的數字序列,如下所示。
如果您想免費試用(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 腳本之前,務必保存您的工作簿,因為更改不能一鍵撤銷。如果遇到宏安全警告,請驗證代碼源並按需啟用宏。在協作環境中,請謹慎操作以免覆蓋重要數據。
總結建議:運行宏後,快速掃描清理後的數據以確保準確性。如果需要國家代碼或分機號碼,請注意這些將被連接到數字字符串中;如有必要,請分別處理這些元素。
相關文章:
最佳辦公效率工具
🤖 | Kutools AI 助手:基於智能執行方式革新數據分析:智能執行 | 生成代碼 | 創建自訂公式 | 分析數據並生成圖表 | 調用 Kutools 函數… |
熱門功能:查找、標記重複值或識別重複項 | 刪除空行 | 合併列或單元格而不丟失數據 | 四捨五入無需公式 ... | |
高級 LOOKUP: 多條件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 .... | |
高級下拉列表:快速創建下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
列管理器: 添加特定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 比較區域和列 ... | |
特色功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿與工作表管理器 | 資源庫(自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按列表發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/斜體/刪除線...) ... | |
頂級 15 種工具集: 12 個文本工具(添加文本、刪除特定字符、...) | 50+ 圖表 類型(甘特圖、...) | 40+ 實用 公式(基於生日計算年齡、...) | 19 個插入工具(插入QR碼、根據路徑插入圖片、...) | 12 個轉換工具(金額轉大寫、匯率轉換、...) | 7 個合併與分割工具(高級合併行、分割儲存格、...) | ... 還有更多 |
使用 Kutools for Excel 提升您的 Excel 技巧,體驗前所未有的高效。 Kutools for Excel 提供超過 300 種高級功能來提高生產力並節省時間。 點擊這裡獲取您最需要的功能...
Office Tab 將標籤式界面帶到 Office,讓您的工作更加輕鬆
- 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中啟用標籤式編輯和閱讀。
- 在同一窗口的新標籤中打開和創建多個文檔,而不是在新窗口中。
- 將您的生產力提高 50%,每天為您減少數百次鼠標點擊!