ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¦r¤¸¨ú¥N

¦r¤¸¨ú¥N

½Ð±Ð¥H¤U¦p¦ó¥Î¨ç¼Æ§PÂ_¤Î¨ú¥N¦r¤¸.
¦r¦ê 145001STM, ¦r¦ê¤¤"00" (¦r¦êªº²Ä4&5½X)¥Î"-"¨ú¥N, -§ó§ï«á¬° 145-1STM
¦P®É§PÂ_, ¦pªG¦r¦ê¬°145010STM , ¦r¦ê¤¤"0"(¦r¦êªº²Ä4½X)¥Î"-"¨ú¥N, -§ó§ï«á¬° 145-10STM
·PÁÂ.
fangac

=REPLACE(A1,4,3-LEN(MID(A1,4,2)*10),"-")
=REPLACE(A1,4,3,TEXT(MID(A1,4,3),"-#"))

145001STM¡@¡Ö¡@145-1STM
145012STM¡@¡Ö¡@145-12STM
145243STM¡@¡Ö¡@145-243STM

TOP

¦^´_ 2# ­ã´£³¡ªL

ÁÂÁª©¥D.
¦]¬°·|¹J¨ì¥H¤Uªºª¬ªp,
145001STM¡@¡Ö¡@145-1STM
145012STM¡@¡Ö¡@145-12STM
210B001STM¡@¡Ö¡@210-1STM
218-xxxx         >     ¤£ÅÜ 218-xxxx
¤£¦b¥H¤W½d³òªº¡Ö¡@¤£ÅÜ 12345xxx
©Ò¥H§â¨ç¼Æ¦ê¦b¤@°_(¦p¤U),¬Ý°_¨Ó¦³ÂI¥d¥d, ¤£¹Lµª®×ÁÙ¬O¥X¨Ó¤F.
=IF(LEFT(F2,4)="210B",(REPLACE(F2,4,3,TEXT(MID(F2,3,1),"-#"))),(IF(LEFT($F2,3)="218",$F2,(IF(ISNUMBER(MID($F2,4,2)*10),(REPLACE($F2,4,3-LEN(MID($F2,4,2)*10),"-")),$F2)))))
·PÁ§A.
fangac

TOP

=LEFT(A1,3)&SUBSTITUTE(SUBSTITUTE(MID(A1,4,2),"00",0),0,"-")&MID(A1,6,99)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

=LEFT(A1,3)&"-"&MID(A1,MATCH(,0/MID(MID(A1,4,9),ROW($1:$9),1),)+3,9)

TOP

¦^´_ 3# fangsc

=LEFT(A1,3)&"-"&MID(A1,5+MAX((MID(A1,5,{1,2,3})=REPT(0,{1,2,3}))*{1,2,3}),99)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : «Ý¤H°h¤@¨B¡A·R¤H¼e¤@¤o¡A´N·|¬¡±o«Ü§Ö¼Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD