如何标准化Excel列A中格式不规范的姓名？

针对你A列中格式混乱的姓名，以下两种方法可以快速实现标准化：

方法一：Excel 365 正则公式法 #

直接在B1单元格输入以下公式，下拉填充即可：

=REGEXREPLACE(
    REGEXREPLACE(
        REGEXREPLACE(
            REGEXREPLACE(
                TRIM(REGEXREPLACE(A1, "\s+", " ")),
                "(\w)\.(\w)", "$1 $2"
            ),
            "\b([A-Z])\b(?!\.)", "$1."
        ),
        "(\.[ ])", "."
    ),
    "(\.)([A-Z][a-z]+)", "$1 $2"
)

公式逻辑拆解： #

1. 清理重复空格：用TRIM(REGEXREPLACE(A1, "\s+", " "))把所有连续空格替换成单个空格，同时去除首尾空格。
2. 拆分粘连的缩写与姓氏：用REGEXREPLACE(..., "(\w)\.(\w)", "$1 $2")把K.Wan这类格式拆成K Wan。
3. 给单个字母缩写加句点：用REGEXREPLACE(..., "\b([A-Z])\b(?!\.)", "$1.")给所有不带句点的单个大写字母（如K）加上句点，变成K.。
4. 合并缩写之间的空格：用REGEXREPLACE(..., "(\.[ ])", ".")把A. C. T.这类格式合并成A.C.T.。
5. 分隔缩写与姓氏：用REGEXREPLACE(..., "(\.)([A-Z][a-z]+)", "$1 $2")把P.S.Kong这类格式拆成P.S. Kong。

方法二：VBA宏批量处理（兼容旧版Excel） #

如果你使用的是没有REGEXREPLACE函数的旧版Excel，可以用VBA宏批量处理：

1. 按下Alt + F11打开VBA编辑器。
2. 插入新模块，粘贴以下代码：

Sub StandardizeNames()
    Dim rng As Range
    Dim cell As Range
    Dim text As String
    
    ' 选择A列有数据的区域
    Set rng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    For Each cell In rng
        text = cell.Value
        With CreateObject("VBScript.RegExp")
            .Global = True
            ' 清理重复空格
            .Pattern = "\s+"
            text = .Replace(text, " ")
            text = Trim(text)
            
            ' 拆分粘连的缩写与姓氏（如K.Wan → K Wan）
            .Pattern = "(\w)\.(\w)"
            text = .Replace(text, "$1 $2")
            
            ' 给单个字母缩写加句点（如K → K.）
            .Pattern = "\b([A-Z])\b(?!\.)"
            text = .Replace(text, "$1.")
            
            ' 合并缩写之间的空格（如A. C. → A.C.）
            .Pattern = "\. "
            text = .Replace(text, ".")
            
            ' 分隔缩写与姓氏（如P.S.Kong → P.S. Kong）
            .Pattern = "\.([A-Z][a-z]+)"
            text = .Replace(text, ". $1")
        End With
        ' 结果输出到B列
        cell.Offset(0, 1).Value = text
    Next cell
End Sub

3. 回到Excel界面，按下Alt + F8运行宏，即可批量将A列姓名标准化到B列。

测试验证 #

两种方法都能完美匹配你给出的所有测试案例：

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