Excel从含中间名、后缀的姓名中提取姓氏的公式咨询

需求说明 #

需要从包含任意格式（含中间名首字母、辈分/荣誉后缀）的姓名字段中，仅提取姓氏，预期效果如下：

• Donald P Bellisario → Bellisario
• Dale Earnhardt Jr → Earnhardt
• Jimmy M Butler III → Butler
• Kanye E West → West
• Joseph Biden → Biden

现有公式问题梳理 #

你之前尝试的3个公式存在两类问题：

• 公式1：=TRIM(RIGHT(SUBSTITUTE(AS9," ",REPT(" ",LEN(AS9))),LEN(AS9)))
• 公式2：=RIGHT(AS9,LEN(AS9)-FIND("*",SUBSTITUTE(AS9," ","*",LEN(AS9)-LEN(SUBSTITUTE(AS9," ","")))))
  以上两个公式仅能提取姓名最后一段内容，无法识别Jr、III这类后缀，会把后缀误判为姓氏。
• 公式3：=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE($AS9,IFERROR(RIGHT($AS9,LEN(AS9)-FIND(" ",$AS9)-10),""),""))," ",REPT(" ","99")),99)),",","")
  该公式设置了10字符的提取长度限制，会截断长度超过10的长姓氏。

可用解决方案 #

方案1：适用于Excel 365/2021及以上版本 #

思路：先枚举所有常见的姓名后缀，批量替换后提取最后一段空格分隔的内容作为姓氏

=LET(
  后缀列表,{"Jr","Sr","II","III","IV","V","VI","VII","VIII"},
  清理后姓名,TRIM(REDUCE(AS9,后缀列表,LAMBDA(姓名,后缀,SUBSTITUTE(" "&姓名&" "," "&后缀&" ","")))),
  分段列表,TEXTSPLIT(清理后姓名," "),
  INDEX(分段列表,COUNTA(分段列表))
)

如果不需要换行可读性，可合并为单行：
=LET(后缀列表,{"Jr","Sr","II","III","IV","V"},清理后姓名,TRIM(REDUCE(AS9,后缀列表,LAMBDA(a,b,SUBSTITUTE(" "&a&" "," "&b&" ","")))),INDEX(TEXTSPLIT(清理后姓名," "),COUNTA(TEXTSPLIT(清理后姓名," "))))

方案2：兼容所有Excel版本的通用公式 #

思路：先逐一把常见后缀替换为空，再用原提取最后一段的逻辑取值

=TRIM(RIGHT(SUBSTITUTE(
  TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
    " "&AS9&" ",
    " Jr "," "),
    " Sr "," "),
    " II "," "),
    " III "," "),
    " IV "," "),
    " V "," "
  )
  ," ",REPT(" ",LEN(AS9))),LEN(AS9)
))

单行写法：
=TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&AS9&" "," Jr "," ")," Sr "," ")," II "," ")," III "," ")," IV "," "))," ",REPT(" ",LEN(AS9))),LEN(AS9)))

提示：如果你的使用场景中存在其他少见后缀，只需在公式的替换列表中按相同格式新增对应后缀即可。

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