开心豆豆 发表于 2021-12-26 11:54:00

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

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

佳丽妈妈 发表于 2022-1-1 10:58:25

=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,"")
数组

快乐每一 发表于 2022-1-8 18:44:56

谢谢，但有小问题

大菠菜 发表于 2022-1-25 19:08:36

=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)),""))

东轩幽兰 发表于 2022-1-27 22:05:13

=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)),""))

用户春风化雨 发表于 2022-2-18 21:15:58

雪地里的耗子 发表于 2022-3-11 23:11:31

估计是不支持UNIQUE函数

狐步猫 发表于 2022-3-20 22:28:14

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

特污兔 发表于 2022-3-22 22:20:12

excel 2016的

红衣车饰销售部 发表于 2022-3-26 00:22:54

看起来只能用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
页: [1]
查看完整版本: 如何将同一个单元格不同的姓名提取到另一单元格