C2{=TEXT(MID(B2,MIN(FIND(ROW($1:$9),B2&1/17)),7),"0-0000-")&MID(B2,MIN(FIND(ROW($1:$9),B2&1/17))+7,LOOKUP(1,0/MID(B2,MIN(FIND(ROW($1:$9),B2&1/17))+ROW($1:$9)+6,1),ROW($1:$9)))
另解{=TEXT(LOOKUP(1,0/ISERR(FIND(".",B10/10^ROW($1:$9))),B10/10^ROW($1:$9)),"000-0000-"&REPT(0,SUM(PERCENTILE(IF(-(0&MID(B10,ROW($1:$19),1))<0,ROW($1:$19)),{1,0})*{1,-1})-6))
留言
張貼留言