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

huangcaiguang

忽悠一个=if(or(Len(c2)={7,12}),replace(c2,Len(c2)-6,,8-6*(--mid(c2,Len(c2)-6,1)=2)),c2)

siguoxiang

=IF(ISNUMBER(FIND("-",C2)),(LEFT(C2,FIND("-",C2)))&(IF(LEN(IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,100),C2))=7,IF((--LEFT(IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,100),C2)))=2,2&(IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,100),C2)),8&(IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,100),C2))),IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,100),C2))),IF(LEN(IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,100),C2))=7,IF((--LEFT(IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,100),C2)))=2,2&(IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,100),C2)),8&(IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,100),C2))),IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,100),C2)))

siguoxiang

                回复答案

kinsey1225

=IF(LEN(C2)=12,SUBSTITUTE(C2,"-","-"&(MID(C2,6,1)*1<>2)*6+2),IF(LEN(C2)=7,((LEFT(C2,1)*1<>2)*6+2)&C2,C2))

ly_122725

公式：=IF(ISERROR(FIND("-",$C2,1)),IF(LEN($C2)=8,$C2,IF(LEFT($C2,1)="2",2&$C2,8&$C2)),IF(LEN($C2)=13,$C2,IF(MID($C2,6,1)="2",LEFT($C2,5)&2&MID($C2,6,100),LEFT($C2,5)&8&MID($C2,6,100))))
太复杂，函数功底太薄弱，还望指点呀。

笨熊猫

1. =IF(OR(LEN(C2)={8,13}),C2,IF(N(C2),"",LEFT(C2,5))&2+(LEFT(RIGHT(D2,7))<>"2")*6&RIGHT(D2,7))

复制代码

ghostjiao

1. =IF(OR(LEN(C2)=7,LEN(C2)=12),IF(LEFT(ABS(-RIGHT(C2,8)))="2",SUBSTITUTE(C2,"2","22",1),IF(LEN(C2)=7,8&C2,REPLACE(C2,5,1,"-8"))),C2)

复制代码

思考了很久，想找到一个简洁的办法，都木有成功，就题论题来一个吧

hsl215

来个干超长的公式
=MID(C2,1,IFERROR(FIND("-",C2),0))&IF(LEN(MID(C2,IFERROR(FIND("-",C2),0)+1,9))=8,"",IF(LEFT(MID(C2,IFERROR(FIND("-",C2),0)+1,9))="2",2,8))&MID(C2,IFERROR(FIND("-",C2),0)+1,9)
再来一个
=IF(LEN(C2)=7,IF(MID(C2,1,1)="2",2,8)&C2,IF(LEN(C2)=12,IF(MID(C2,6,1)="2",SUBSTITUTE(C2,"-","-2"),SUBSTITUTE(C2,"-","-8")),C2))

doggie

=IF(N(C2),,"0769-")&-SUBSTITUTE("-8"&RIGHT(C2,7),"-82","-22")

优化一下：
=IF(N(C2),,"0769-")&-SUBSTITUTE(-8&RIGHT(C2,7),-82,-22)

cleverzhzhf

写一个稍微费解的：

1. =REPLACE(C2,LEN(C2)-7+1,,IF(OR(LEN(C2)={7,12}),IF(LEFT(TEXT(RIGHT(C2,8),"0;0"))-2,8,2),""))

复制代码