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

雪舞子

多谢忽悠老师出题,重在参与:

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

复制代码

E小影

=IF(OR(LEN(C2)={8,13}),C2,REPLACE(C2,LEN(C2)-6,,IF(RIGHT(C2,7)<"3",2,8)))

wlc520113000

=IF(-LEFT(C2),,"0769-")&RIGHT(IF(LEFT(RIGHT(C2,7))-2,8,2)&ABS(RIGHT(C2,8)),8)
=IF(OR(LEN(C2)={8,13}),C2,REPLACE(C2,MOD(LEN(C2),7)+1,,IF(LEFT(RIGHT(C2,7))-2,8,2)))
=IF(OR(LEN(C2)={8,13}),C2,REPLACE(C2,IF(LEN(C2)>7,6,1),,IF(LEFT(RIGHT(C2,7))-2,8,2)))
=IF(OR(LEN(C2)={8,13}),C2,REPLACE(C2,IF(LEN(C2)>7,6,1),,IF(ISERR(FIND("-2","-"&C2)),8,2)))
=IF(ISERROR(FIND("-",C2)),"","0769-")&INDEX({"","",8,2},BIN2DEC(N(LEN(ABS(RIGHT(C2,8)))=7)&N((LEFT(ABS(RIGHT(C2,8)))="2")))+1)&ABS(RIGHT(C2,8))

孤单无所谓

=REPLACE(C2,LEN(C2)-6,,IF(OR(LEN(C2)={8,13}),,IF(LEFT(RIGHT(C2,7))-2,8,2)))

yqnh

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

复制代码

参与 学习

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

复制代码

xhwts

学习一下=IF(LEN(C2)<8,IF(MID(RIGHT(C2,7),1,1)*1=2,2&RIGHT(C2,7),8&RIGHT(C2,7)),"0769-"&IF(MID(RIGHT(C2,7),1,1)*1=2,2&RIGHT(C2,7),8&RIGHT(C2,7)))

wupeiqi

=IF(LEN(C2)<=8,IF(LEN(C2)=8,C2,IF(LEFT(C2,1)="2",2&C2,8&C2)),IF(LEN(C2)-FIND("-",C2)<=8,IF(LEN(C2)-FIND("-",C2)=8,C2,IF(MID(C2,FIND("-",C2)+1,1)="2",LEFT(C2,FIND("-",C2))&"2"&RIGHT(C2,LEN(C2)-FIND("-",C2)),LEFT(C2,FIND("-",C2))&"8"&RIGHT(C2,LEN(C2)-FIND("-",C2))))))

笨笨四

SUBSTITUTE(SUBSTITUTE(IF(OR(LEN(C2)={13,8}),C2,REPLACE(C2,LEN(C2)-6,,8%)),8.2%,22),8%,8)

wp8680

1. =IF(LEFT(C2)="0","0769-","")&RIGHT(IF(LEFT(IF(LEN(C2)>9,SUBSTITUTE(C2,"0769-",""),C2))="2",2,8)&IF(LEN(C2)>9,SUBSTITUTE(C2,"0769-",""),C2),8)

复制代码

wp8680

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

复制代码