[已开]201401函中课前练习11:生成新报价

ldxhzy

B2公式：
=IFERROR(LEFT(A2,FIND("/",A2)-1)+100*(--LEFT(A2,FIND("/",A2)-1)>0) & "/" & MID(A2,FIND("/",A2)+1,5)+100*(--MID(A2,FIND("/",A2)+1,5)>0),A2+100*(A2>0))

《渐行渐远》

《渐行渐远》 发表于 2014-8-6 10:23
=IF(ISERR(FIND("/",A2)),IF(A2=0,0,A2+100),IF(--LEFT(A2,FIND("/",A2)-1)=0,0,LEFT(A2,FIND("/",A2)-1)+1 ...

=IF(ISERR(FIND("/",A2)),A2+100*(A2>0),LEFT(A2,FIND("/",A2)-1)+100*(--LEFT(A2,FIND("/",A2)-1)>0)&"/"&RIGHT(A2,LEN(A2)-FIND("/",A2))+100*(--RIGHT(A2,LEN(A2)-FIND("/",A2))>0))  
两个都一样 没取巧{:03:}

珺珺妈

=IF(A2=0,0,IF(A2<9^9,A2+100,IFERROR(MAX(--TEXT(LEFT(A2,{1,2,3,4,5}),"0;;;\0"))+100,0)&"/"&IF(RIGHT(A2,2)="/0","0",MAX(--TEXT(RIGHT(A2,{3,4,5}),"0;;\0;\0"))+100)))

天龙九部

1. SUBSTITUTE(IF(A2=0,0,IF(ISNUMBER(A2),A2+100,(LEFT(SUBSTITUTE(A2,"/",REPT(" ",9)),5)+100)&"/"&(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",9)),5)+100))),100,0)

复制代码

路边的记忆

上来冒个泡泡

1. =IF(COUNT(A2),TEXT(A2+100,"[=100]!0"),TEXT(LEFT(A2,FIND("/",A2&"/")-1)+100,"[=100]!0")&"/"&TEXT((0&MID(A2,FIND("/",A2&"/")+1,99))+100,"[=100]!0"))

复制代码

妹纸

=IFERROR(IF(--LEFT(A2,FIND("/",A2)-1)=0,0,LEFT(A2,FIND("/",A2)-1)+100)&"/"&IF(--RIGHT(A2,LEN(A2)-FIND("/",A2))=0,0,RIGHT(A2,LEN(A2)-FIND("/",A2))+100),IF(A2=0,0,A2+100))   比不了字符{:091:}

cgzxwwf

1. =IF(A2=0,0,IF(ISERROR(FIND("/",A2)),A2+100,IF(LEFT(A2,FIND("/",A2)-1)*1=0,0,LEFT(A2,FIND("/",A2)-1)+100)&"/"&IF(RIGHT(A2,LEN(A2)-FIND("/",A2))*1=0,0,RIGHT(A2,LEN(A2)-FIND("/",A2))+100)))

复制代码

比一比，看谁更长

MCQUEEN

写了二个超长的，各种弱，我就是来响应组长号召的，为组长而战

1. =IFERROR(-LOOKUP(,-LEFT(A2,ROW($1:$5)))+(-LEFT(A2,1)<>0)*100&"/"&RIGHT(A2,LEN(A2)-FIND("/",A2))+(RIGHT(A2,2)<>"/0")*100,A2+(A2>0)*100)

复制代码

1. =IF(A2=0,,IFERROR(IF(--LEFT(A2,FIND("/",A2)-1),LEFT(A2,FIND("/",A2)-1)+100,0)&"/"&IF(--RIGHT(A2,LEN(A2)-FIND("/",A2)),RIGHT(A2,LEN(A2)-FIND("/",A2))+100,0),A2+100))

复制代码

hsl215

来个超长的捧个场！

1. =IF(-MID(A2,1,FIND("/",A2&"/")-1)=0,0,MID(A2,1,FIND("/",A2&"/")-1)+100)&IF(LEN(D2)>0,"/"&IF(MID(A2,FIND("/",A2&"/")+1,9)="","",IF(-MID(A2,FIND("/",A2&"/")+1,9)=0,0,MID(A2,FIND("/",A2&"/")+1,9)+100)),"")

复制代码

yqnh

时间紧,参与学习
=IFERROR(TEXT(MID(SUBSTITUTE(A2&"/","/",REPT(" ",9)),1,9),"0;;-100")+100&"/"&TEXT(MID(SUBSTITUTE(A2&"/","/",REPT(" ",9)),11,9),"0;;-100")+100,IF(A2,A2+100,A2))