Excel提取姓氏相同、名字不同的人名至指定单元格需求

适用于Excel 365/2021（动态数组版本） #

如果你的Excel支持动态数组函数，直接在目标单元格（比如B1）输入以下公式即可得到结果：

=TEXTJOIN("; ",TRUE,FILTER(TEXTSPLIT(A1,"; "),COUNTIF(TEXTSPLIT(A1,"; "),LEFT(TEXTSPLIT(A1,"; "),FIND(" ",TEXTSPLIT(A1,"; "))-1)&"*")>1))

公式说明： #

• TEXTSPLIT(A1,"; ")：将A1中的人名按; 分隔为单个元素的数组
• LEFT(人名,FIND(" ",人名)-1)：提取每个人名的姓氏部分
• COUNTIF(..., 姓氏&"*")>1：筛选出姓氏出现次数超过1次的人名
• TEXTJOIN("; ",TRUE,...)：将筛选后的人名用; 拼接成字符串

适用于旧版Excel（无动态数组） #

如果你的Excel不支持动态数组，用辅助列分步实现：

• 拆分人名到辅助列：在C1输入公式，下拉直到出现空值：

  =TRIM(MID(SUBSTITUTE($A1,"; ",REPT(" ",99)),(ROW(A1)-1)*99+1,99))
  

• 提取姓氏：在D1输入公式，下拉填充：

  =LEFT(C1,FIND(" ",C1)-1)
  

• 统计姓氏出现次数：在E1输入公式，下拉填充：

  =COUNTIF($D$1:$D$6,D1)
  

• 拼接符合条件的人名：在B1输入数组公式，按Ctrl+Shift+Enter确认：

  =TEXTJOIN("; ",TRUE,IF($E$1:$E$6>1,$C$1:$C$6,""))
  

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