题目

我想从EXCEL筛选出手机号码是AAAAA,AAAA,AAA,AA,AAAB,AABB,ABAB,ABCD,ABCDE

提问时间：2021-02-16

答案

看了半天也没有找出有效的逻辑规律：
假设数据在A列：
B1单元格公式：
=SUMPRODUCT((RIGHT(A1,ROW($2:$5))/REPT(1,ROW($2:$5))=--RIGHT(A1))*1)-1
格式柄下拉复制公式
C1单元格公式：
=IF(B1>0,CHOOSE(B1,"AAA","AAAA","AAAAA"),IF(AND(B1=0,LEN(RIGHT(A1,2)/11)=1,LEN(MID(A1,8,2)/11)=1),"AABB",IF(AND(B1=0,LEN(RIGHT(A1,2)/11)=1),"AA",IF(LEN(MID(A1,8,3)/111)=1,"AAAB",IF(MID(A1,8,2)=MID(A1,10,2),"ABAB","")))))&IF(ISNUMBER(FIND(RIGHT(A1,5),"0123456789")),"ABCDE",IF(ISNUMBER(FIND(RIGHT(A1,4),"0123456789")),"ABCD",""))
格式柄下拉复制公式