一个单元格中的字段,抓取特定的字段,并合并在新单元格中

cfsonic · 发表于 2024-3-19 20:55

入主题所示,

excel 2016 , 不能使用textsplit,textbefore , textafter , 请教如何用excel公式实现

quqiyuan · 发表于 2024-3-19 22:15

就函数就是长
=SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IFERROR(LEFT(TRIM(MID(SUBSTITUTE(G3,";",REPT(" ",199)),COLUMN($A:$J)*199-198,199)),FIND(",",TRIM(MID(SUBSTITUTE(G3,";",REPT(" ",199)),COLUMN($A:$J)*199-198,199)))-1),"")&","),","," "))," ",",")

tmplinshi · 发表于 2024-3-19 22:20

本帖最后由 tmplinshi 于 2024-3-19 22:37 编辑

=CONCAT(REPLACE(LEFT(G3,FIND("@",SUBSTITUTE(G3,"(","@",ROW(1:5)))-1),1,FIND("@",SUBSTITUTE(";"&G3,";","@",ROW(1:5)))-1,))

复制代码

cfsonic · 发表于 2024-3-19 22:26

quqiyuan 发表于 2024-3-19 22:15
就函数就是长
=SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IFERROR(LEFT(TRIM(MID(SUBSTITUTE(G3,";",REPT(" ",19 ...

多个问题, 如果有重复项, 如何去除,我添加了Unique函数貌似不起作用~!

quqiyuan · 发表于 2024-3-19 22:32

cfsonic 发表于 2024-3-19 22:26
多个问题, 如果有重复项, 如何去除,我添加了Unique函数貌似不起作用~!

加了
=SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(UNIQUE(IFERROR(LEFT(TRIM(MID(SUBSTITUTE(G3,";",REPT(" ",199)),COLUMN($A:$J)*199-198,199)),FIND(",",TRIM(MID(SUBSTITUTE(G3,";",REPT(" ",199)),COLUMN($A:$J)*199-198,199)))-1),""),1)&","),","," "))," ",",")

tmplinshi · 发表于 2024-3-19 22:40

本帖最后由 tmplinshi 于 2024-3-19 23:13 编辑

cfsonic 发表于 2024-3-19 22:26
多个问题, 如果有重复项, 如何去除,我添加了Unique函数貌似不起作用~!

=LET(
s, G3,
i, SEQUENCE(LEN(s) - LEN(SUBSTITUTE(s, "(", ))),
b, FIND("@", SUBSTITUTE(";" & s, ";", "@", i)) - 1,
e, FIND("@", SUBSTITUTE(s, "(", "@", i)) - 1,
CONCAT(UNIQUE(REPLACE(LEFT(s, e), 1, b, )))
)

复制代码

tmplinshi · 发表于 2024-3-19 22:56

=CONCAT(UNIQUE(FILTERXML("<r>"&SUBSTITUTE(";"&SUBSTITUTE(G3,"(","</a>"),";","<a>")&"</r>","//a")))

复制代码

qydn · 发表于 2024-3-19 23:20

试试这个试试这个

		自动登录	找回密码
密码			免费注册

[求助] 一个单元格中的字段,抓取特定的字段,并合并在新单元格中

评分

评分

评分

评分