Question

EXCEL如何提取多个字符当中任何一个字符第一次出现的位置？

1、先确定图中“区”、“县”、“知”三个字符中的任意一个字符在单元格的位置；2、再确定第一个“1”字的位置3、再提取上述1和2两个位置中间的内容，填写在E列的姓名栏。

Answer 1

参考下面

Excel如何从一段文字中自动提取相关信息

Answer 2

1.  =MAX(IF(COUNTIF(A2,"*区*"),FIND("区",A2),0),IF(COUNTIF(A2,"*县*"),FIND("县",A2),0),IF(COUNTIF(A2,"*知*"),FIND("知",A2),0))

2. =FIND(1,A2)

3. =MID(A2,MAX(IF(COUNTIF(A2,"*区*"),FIND("区",A2),0),IF(COUNTIF(A2,"*县*"),FIND("县",A2),0),IF(COUNTIF(A2,"*知*"),FIND("知",A2),0))+1,FIND(1,A2)-MAX(IF(COUNTIF(A2,"*区*"),FIND("区",A2),0),IF(COUNTIF(A2,"*县*"),FIND("县",A2),0),IF(COUNTIF(A2,"*知*"),FIND("知",A2),0))-1)
   

Answer 3

=IF(ISERROR(MID(A2,IF(ISERROR(IF(ISERROR(FIND("县",A2,1)),FIND("区",A2,1),FIND("县",A2,1))),FIND("知",A2,1),IF(ISERROR(FIND("县",A2,1)),FIND("区",A2,1),FIND("县",A2,1)))+1,FIND("1",A2,1)-IF(ISERROR(IF(ISERROR(FIND("县",A2,1)),FIND("区",A2,1),FIND("县",A2,1))),FIND("知",A2,1),IF(ISERROR(FIND("县",A2,1)),FIND("区",A2,1),FIND("县",A2,1)))-1)),"",MID(A2,IF(ISERROR(IF(ISERROR(FIND("县",A2,1)),FIND("区",A2,1),FIND("县",A2,1))),FIND("知",A2,1),IF(ISERROR(FIND("县",A2,1)),FIND("区",A2,1),FIND("县",A2,1)))+1,FIND("1",A2,1)-IF(ISERROR(IF(ISERROR(FIND("县",A2,1)),FIND("区",A2,1),FIND("县",A2,1))),FIND("知",A2,1),IF(ISERROR(FIND("县",A2,1)),FIND("区",A2,1),FIND("县",A2,1)))-1))

Answer 4

方法之一

Answer 5

假设数据在单元格A1
1、=SUM(IFERROR(FIND({"区","县","知"},A1),0))
ctrl+shift+enter结束输入
2、=FIND("1",A1)
3、=MID(A1,SUM(IFERROR(FIND({"区","县","知"},A1),0))+1,FIND("1",A1)-SUM(IFERROR(FIND({"区","县","知"},A1),0))-1)
ctrl+shift+enter结束输入