在excel中怎么用公式筛选出符合条件的尾号类型?比如下图都是手机后四位的部分尾号截图,我想通过公式筛选出尾号分别是AA,AAA,AAAA,ABC,ABCD,ABAB,AAAB,ABBA的手机尾号,公式分别应该怎么写??
ABC就类似于123,567
追答=IF(RIGHT(A2,3)=VALUE(RIGHT(A2,1))-2&VALUE(RIGHT(A2,1))-1&VALUE(RIGHT(A2,1)),"ABC",IF(RIGHT(A2,4)=VALUE(RIGHT(A2,1))-3&VALUE(RIGHT(A2,1))-2&VALUE(RIGHT(A2,1))-1&VALUE(RIGHT(A2,1)),"ABCD",IF(RIGHT(A2,4)=VALUE(RIGHT(A2,1))-1&VALUE(RIGHT(A2,1))&VALUE(RIGHT(A2,1))-1&VALUE(RIGHT(A2,1)),"ABAB",IF(RIGHT(A2,4)=VALUE(RIGHT(A2,1))-1&VALUE(RIGHT(A2,1))-1&VALUE(RIGHT(A2,1))-1&VALUE(RIGHT(A2,1)),"AAAB",IF(RIGHT(A2,4)=VALUE(RIGHT(A2,1))&VALUE(RIGHT(A2,1))+1&VALUE(RIGHT(A2,1))+1&VALUE(RIGHT(A2,1)),"ABBA",IF(RIGHT(A2,4)=REPT(RIGHT(A2,1),4),"AAAA",IF(RIGHT(A2,3)=REPT(RIGHT(A2,1),3),"AAA",IF(RIGHT(A2,2)=REPT(RIGHT(A2,1),2),"AA",""))))))))
追问还有个问题请教,如果我只想筛选尾号是66或者88的尾号,或者是666或888的尾号,公式要怎么写呢?
追答=IF(RIGHT(A2,2)={"66";"88"},"6688","")
=IF(RIGHT(A2,3)={"666";"888"},"666888","")