练习十四【先开帖，后评分】

ddhhyy16

判断电话号码是否为7位，如果为7位则执行升位，否则保持号码不变
再判断7位号码的首位是否是2，如果是2，则号码前面加2 否则加8

XMergeD

1. =IF(LEFT(C2)%,,"0769-")&IF(LEFT(RIGHT(C2,7))-2,8,2)&RIGHT(C2,7)

复制代码

baksy

=IF(((LEN(C2)>8)*(LEN(ABS(RIGHT(C2,8)))=8)+(LEN(C2)=8)),C2,IF(LEN(C2)>8,LEFT(C2,5),"")&IF(LEFT(RIGHT(C2,7))="2",2&RIGHT(C2,7),8&RIGHT(C2,7)))

tianyiyi

来个超长公式

1. =IF(ISERR(FIND("-",C2)),IF(LEN(SUBSTITUTE(C2,"0769-",))=7,IF(LEFT(SUBSTITUTE(C2,"0769-",))="2",2&SUBSTITUTE(C2,"0769-",),8&SUBSTITUTE(C2,"0769-",)),SUBSTITUTE(C2,"0769-",)),"0769-"&IF(LEN(SUBSTITUTE(C2,"0769-",))=7,IF(LEFT(SUBSTITUTE(C2,"0769-",))="2",2&SUBSTITUTE(C2,"0769-",),8&SUBSTITUTE(C2,"0769-",)),SUBSTITUTE(C2,"0769-",)))

复制代码

独奏

=IF(LEN(SUBSTITUTE(C2,"0769-",""))<8,IF(ISERR(FIND("-",C2)),IF(LEFT(C2,1)="2",2&C2,8&C2),IF(RIGHT(LEFT(C2,6),1)="2",REPLACE(C2,6,,2),REPLACE(C2,6,,8))),C2)

风林火山

1. =IF(LEN(D2)>8,"0769-","")&SUBSTITUTE(8&RIGHT(C2,7),82,22,1)

复制代码

hongqiqqq

1. =IF(LEFT(RIGHT("-"&C2,8))<>"-",C2,IF(--LEFT(RIGHT(C2,7))=2,REPLACE(C2,LEN(C2)-6,,"2"),REPLACE(C2,LEN(C2)-6,,"8")))

复制代码

zhyoucheng

1. =IF(OR(LEN(C2)=13,LEN(C2)=8),C2,IF(LEFT(RIGHT(D2,7),1)="2",MID(C2,1,LEN(C2)-7)&"2"&RIGHT(D2,7),MID(C2,1,LEN(C2)-7)&"8"&RIGHT(D2,7)))

复制代码

ly258

=IF(IFERROR(FIND("-",D2),0)>0,MID(D2,1,5)&IF(LEN(MID(D2,6,LEN(D2)))=8,MID(D2,6,LEN(D2)),"8"&MID(D2,6,LEN(D2))),IF(LEN(D2)=8,D2,"8"&D2))

jipei237582527

做的有点复杂，但是我已经尽力了，呵呵