需要用公式完成,A 列是原数据,B列是通过公式得到的结果, C 列 和D 列是替换条件。 如图 A替换成1,B替换成2.。。。。。, 所有A1里的A,B,C替换后变成B1里的1,2,3. 如果A 里的数据在C列里找不到,就不替换,保留原始数据,比如A7,A8,A9,希望大神能帮忙,多谢
想了下,根据单元格字母个数用VLOOKUP提取的办法更实用,在B1单元格输入公式=IFERROR(IF(LEN(SUBSTITUTE(A1,",",))=1,VLOOKUP(LEFT(A1,1),C:D,2,),IF(LEN(SUBSTITUTE(A1,",",))=2,VLOOKUP(LEFT(A1,1),C:D,2,)&","&VLOOKUP(MID(A1,3,1),C:D,2,),IF(LEN(SUBSTITUTE(A1,",",))=3,VLOOKUP(LEFT(A1,1),C:D,2,)&","&VLOOKUP(MID(A1,3,1),C:D,2,)&","&VLOOKUP(MID(A1,5,1),C:D,2,),IF(LEN(SUBSTITUTE(A1,",",))=4,VLOOKUP(LEFT(A1,1),C:D,2,)&","&VLOOKUP(MID(A1,3,1),C:D,2,)&","&VLOOKUP(MID(A1,5,1),C:D,2,)&","&VLOOKUP(MID(A1,7,1),C:D,2,),IF(LEN(SUBSTITUTE(A1,",",))=5,VLOOKUP(LEFT(A1,1),C:D,2,)&","&VLOOKUP(MID(A1,3,1),C:D,2,)&","&VLOOKUP(MID(A1,5,1),C:D,2,)&","&VLOOKUP(MID(A1,7,1),C:D,2,)&","&VLOOKUP(MID(A1,9,1),C:D,2,),""))))),A1)
下拉公式。(以上公式为你写到如A1单元格中5个字母的长度,不够的话可自行模仿添加公式即可) 如图:
示例
数组公式:(按Ctrl+Shift+Enter)
=TEXTJOIN(" ",,MID("= "&FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A4,"/&"," OR </b><b>"),"&"," AND </b><b>")&"</b></a>","//b"),{3,1},{3,99}))
=TEXTJOIN(" ",,MID("= '"&FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,"/&","' OR</b><b>"),"&","' AND</b><b>")&"</b></a>","//b"),{4,1},{3,99}))&"'"
下面的带单引号的