如何将同一个单元格不同的姓名提取到另一单元格

开心豆豆

如何将同一个单元格不同的姓名提取到另一单元格如图

佳丽妈妈

=REPLACE(A1,MIN(IF(ROUNDUP(FIND(MID(A1,{1,2,3,4,5}*4-3,3),A1)/4,)={1,2,3,4,5},9,{1,2,3,4,5}))*4-3,4,"")
数组

快乐每一

谢谢，但有小问题

大菠菜

=TEXTJOIN(" ",1,IF(MATCH(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",19)),ROW($1:$9)*19-18,19)),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",19)),ROW($1:$9)*19-18,19)),)=ROW($1:$9),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",19)),ROW($1:$9)*19-18,19)),""))

东轩幽兰

=TEXTJOIN(" ",1,IFERROR(UNIQUE(MID($A1,FIND("@",SUBSTITUTE(" "&$A1&"  "," ","@",ROW($1:$99))),FIND("@",SUBSTITUTE(" "&$A1&"  "," ","@",ROW($1:$99)+1))-FIND("@",SUBSTITUTE(" "&$A1&"  "," ","@",ROW($1:$99)))-1)),""))

用户春风化雨

雪地里的耗子

估计是不支持UNIQUE函数

狐步猫

升级WPS 最新版本或是 OFFICE 365 公式
=TRANSPOSE(TEXTJOIN(" ",,UNIQUE(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",))+1))*99-98,99)))))

特污兔

excel 2016的

红衣车饰销售部

看起来只能用4楼大佬的方法配合自定义函数才能解了
TEXTJOIN>>>> CONCATY
=ConcatY(IF(MATCH(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",19)),ROW($1:$9)*19-18,19)),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",19)),ROW($1:$9)*19-18,19)),)=ROW($1:$9),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",19)),ROW($1:$9)*19-18,19)),""))

Function ConcatY(FinalRng As Variant) As Variant
First = True
ConcatY = ""
For Each c In FinalRng
  If c <> "" Then
  If First = True Then
    ConcatY = c
    First = False
  Else
    ConcatY = ConcatY & " " & c
  End If
  End If
Next c
End Function