特定位置提取字符

boubing · 发表于 2024-2-27 16:02

各位老师，如示例表格，需要在A列字符串中，最后一个逗号,右侧的字符串内，查找是否含有sheet2中A2：A4的字符，如果有，则提取sheet1中A列最后一个逗号,右侧的字符串到C列，否则返回空白。模拟的结果在B列。恳请老师不吝赐教

solextrade · 发表于 2024-2-27 16:11

=REPT(TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",19)),19)),SUMPRODUCT(COUNTIF(A2,"*,*"&Sheet2!A$2:A$4&"*")))

袁勇 · 发表于 2024-2-27 16:12

数组公式：

=IF(OR(ISNUMBER(FIND(Sheet2!A$2:A$4,TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",50)),50))))),TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",50)),50)),"")

复制代码

静听溪流 · 发表于 2024-2-27 16:13

=IF(COUNT(FIND(Sheet2!$A$2:$A$4,TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",99)),99)))),TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",99)),99)),"")

袁勇 · 发表于 2024-2-27 16:14

需要在A列字符串中，最后一个逗号,右侧的字符串内，查找是否含有sheet2中A2：A4的字符》》》》》》》》》》转化为公式：OR(ISNUMBER(FIND(Sheet2!A$2:A$4,TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",50)),50)))))

象山海鲜 · 发表于 2024-2-27 16:19

就例子取巧

复制代码

jisijie630 · 发表于 2024-2-27 16:45

=MID(A2,IF(AND(ISERR(FIND(Sheet2!A$2:A$4,RIGHT(SUBSTITUTE(A2,",",REPT(" ",9)),9)))),999,LOOKUP(9^9,FIND(",",A2,ROW($1:$999)))+1),99)

朝露孤烟 · 发表于 2024-2-27 16:46

本帖最后由朝露孤烟于 2024-2-27 17:57 编辑

=IFERROR(RIGHT(A2,LEN(A2)-FIND(CHAR(34)&",",A2&CHAR(34)&",")-1),"")有点投机。

E花E世界 · 发表于 2024-2-27 17:27

复制代码

mykitami · 发表于 2024-2-27 18:23

=REPT(TEXTAFTER(A2,",",-1),SUM(N(ISNUMBER(FIND(TRANSPOSE(Sheet2!$A$2:$A$4),TEXTAFTER(A2,",",-1)))))=1)

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

[已解决] 特定位置提取字符