Excel函数与公式

boubing Lv.2

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

特定位置提取字符.rar   2024-2-27 16:01 上传

10.55 KB, 下载次数: 25

395阅读
17回复 倒序

solextrade Lv.7 2楼

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

袁勇 Lv.7 3楼

数组公式:
  1. =IF(OR(ISNUMBER(FIND(Sheet2!A$2:A$4,TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",50)),50))))),TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",50)),50)),"")

静听溪流 Lv.6 4楼

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

袁勇 Lv.7 5楼

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

象山海鲜 Lv.18 6楼

就例子取巧

  1. =MID(A2,FIND(""",",A2&""",")+2,99)

jisijie630 Lv.7 7楼

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

朝露孤烟 Lv.2 8楼

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

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

E花E世界 Lv.3 9楼

image.png
  1. =TEXTAFTER(A2,""",",,,,"")

mykitami Lv.5 10楼

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