Excel批量标准化多格式国内外电话号码（VBA/函数方案）

需求回顾 #

• 国内号码：转换为###-###-####格式，必须确保-是单元格实际存储的字符串内容（而非仅显示格式）
• 英国号码：转换为+##-##-####-####格式，必须确保+和-是单元格实际存储的字符串内容
• 处理规模：千行级数据集，需支持每月复用，兼容原始数据的多样格式（含各种分隔符、前缀变体）

高效复用解决方案 #

方案一：通用VBA脚本（推荐，适合批量复用） #

该脚本先统一清洗原始数据，再根据号码特征自动识别类型并格式化，完全适配多样格式的输入。

步骤1：打开Excel文件，按Alt+F11打开VBA编辑器

步骤2：插入模块，粘贴以下代码

Sub StandardizePhoneNumbers()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim cleanNum As String
    Dim i As Integer
    
    ' 设置要处理的工作表（可根据实际修改）
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' 设置要处理的列（假设号码在A列，从第2行开始，可修改）
    Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    
    For Each cell In rng
        If cell.Value <> "" Then
            ' 步骤1：清洗数据——保留数字和开头的+号，移除所有其他字符（-、空格、()、.等）
            cleanNum = ""
            For i = 1 To Len(cell.Value)
                Select Case Mid(cell.Value, i, 1)
                    Case "0" To "9", "+"
                        cleanNum = cleanNum & Mid(cell.Value, i, 1)
                End Select
            Next i
            
            ' 步骤2：识别号码类型并格式化
            ' 国内号码规则：11位纯数字（或开头带+86/0086，需先转换为11位纯数字）
            If (Len(cleanNum) = 11 And IsNumeric(cleanNum)) Or _
               (Left(cleanNum, 3) = "+86" And Len(cleanNum) = 13) Or _
               (Left(cleanNum, 4) = "0086" And Len(cleanNum) = 14) Then
                ' 统一转为11位纯数字
                If Left(cleanNum, 3) = "+86" Then
                    cleanNum = Mid(cleanNum, 4, 11)
                ElseIf Left(cleanNum, 4) = "0086" Then
                    cleanNum = Mid(cleanNum, 5, 11)
                End If
                ' 格式化为###-###-####
                cell.Value = Mid(cleanNum, 1, 3) & "-" & Mid(cleanNum, 4, 3) & "-" & Mid(cleanNum, 7, 4)
            
            ' 英国号码规则：开头带+44/0044，或10位数字（默认补+44）
            ElseIf (Left(cleanNum, 3) = "+44" And Len(cleanNum) = 12) Or _
                   (Left(cleanNum, 4) = "0044" And Len(cleanNum) = 13) Or _
                   (Len(cleanNum) = 10 And IsNumeric(cleanNum)) Then
                ' 统一转为+44开头的12位格式（+44+10位数字）
                If Left(cleanNum, 4) = "0044" Then
                    cleanNum = "+" & Mid(cleanNum, 3, 10)
                ElseIf Len(cleanNum) = 10 Then
                    cleanNum = "+44" & cleanNum
                End If
                ' 格式化为+##-##-####-####
                cell.Value = Left(cleanNum, 3) & "-" & Mid(cleanNum, 4, 2) & "-" & Mid(cleanNum, 6, 4) & "-" & Mid(cleanNum, 10, 4)
            
            ' 其他未匹配号码（可根据需求扩展规则）
            Else
                cell.Value = "未识别格式：" & cell.Value
            End If
        End If
    Next cell
End Sub

步骤3：修改脚本中的工作表和列参数

根据你的实际数据位置，修改Set ws = ThisWorkbook.Worksheets("Sheet1")和Set rng = ws.Range("A2:A" & ...)中的工作表名称和列范围。

步骤4：运行脚本

按F5执行，处理完成后可通过双击单元格验证：特殊字符+和-已实际存入字符串，而非仅显示格式。

方案二：Power Query（适合无VBA基础用户） #

Power Query可可视化完成数据清洗和格式化，同样支持每月复用（可保存查询模板）。

步骤1：导入数据到Power Query

选中号码列 → 「数据」选项卡 → 「从表格/区域」（勾选「我的表格有标题」）

步骤2：清洗数据

添加自定义列，输入公式清除非数字和+号的字符：

Text.Combine(List.Select(Text.ToList([电话号码]), each _ = "+" or List.Contains({"0".."9"}, _)))

替换原列，删除多余列

步骤3：分类格式化

添加条件列：

• 条件1：Text.Length([清洗后号码])=11 or Text.StartsWith([清洗后号码],"+86") or Text.StartsWith([清洗后号码],"0086")
  输出：Text.Range([清洗后号码], if Text.StartsWith([清洗后号码],"+86") then 3 else if Text.StartsWith([清洗后号码],"0086") then 4 else 0, 3) & "-" & Text.Range([清洗后号码], if Text.StartsWith([清洗后号码],"+86") then 6 else if Text.StartsWith([清洗后号码],"0086") then 7 else 3, 3) & "-" & Text.Range([清洗后号码], if Text.StartsWith([清洗后号码],"+86") then 9 else if Text.StartsWith([清洗后号码],"0086") then 10 else 6, 4)
• 条件2：Text.StartsWith([清洗后号码],"+44") or Text.StartsWith([清洗后号码],"0044") or Text.Length([清洗后号码])=10
  输出：let clean = if Text.StartsWith([清洗后号码],"0044") then "+" & Text.Range([清洗后号码],2,10) else if Text.Length([清洗后号码])=10 then "+44" & [清洗后号码] else [清洗后号码] in Text.Range(clean,0,3) & "-" & Text.Range(clean,3,2) & "-" & Text.Range(clean,5,4) & "-" & Text.Range(clean,9,4)
• 否则："未识别格式：" & [电话号码]

步骤4：加载回Excel

关闭Power Query，加载数据到新工作表，验证特殊字符已存入实际字符串。

方案优势 #

• 覆盖绝大多数常见格式变体（含各种分隔符、前缀）
• 特殊字符直接存入单元格字符串，完全适配CRM导入要求
• 可保存脚本/查询模板，每月直接复用，无需重复配置
• 处理千行级数据仅需几秒，效率远超手动或函数方法

内容的提问来源于stack exchange，提问作者gnivil