试试这个公式。这是对你的公式的轻微修改。它的作用是,搜索第一个 @ 符号,然后将其替换为 ~~ 并提取包含的单词。然后它搜索第二个@符号并执行相同的操作。
=CONCATENATE(TRIM(LEFT(SUBSTITUTE(MID(B3,FIND("~~",SUBSTITUTE(B3,"@","~~",1)),LEN(B3))," ",REPT(" ",100),1),100)),",",TRIM(LEFT(SUBSTITUTE(MID(B3,FIND("~~",SUBSTITUTE(B3,"@","~~",2)),LEN(B3))," ",REPT(" ",100),1),100)))
如果您想添加第三个@单词来提取,只需添加 CONCATENATE 的另一个实例,然后替换它IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("~~",SUBSTITUTE(B4,"@","~~",2(for third word replace value 2 with 3)),LEN(B4))
第三次出现的公式的一部分。因此,要提取 3 个值,请使用此公式。 (我添加了 IFERROR 部分,以防找不到单词@)
=CONCATENATE(IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("~~",SUBSTITUTE(B4,"@","~~",1)),LEN(B4))," ",REPT(" ",100),1),100)),""),",",IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("~~",SUBSTITUTE(B4,"@","~~",2)),LEN(B4))," ",REPT(" ",100),1),100)),""),",",IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B4,FIND("~~",SUBSTITUTE(B4,"@","~~",3)),LEN(B4))," ",REPT(" ",100),1),100)),""))