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

[µo°Ý] ´£¨ú½s¸¹¤¤ªº¼Æ¦r&¼Æ¦r¸É¹s

[µo°Ý] ´£¨ú½s¸¹¤¤ªº¼Æ¦r&¼Æ¦r¸É¹s

´£¨ú½s¸¹¤¤ªº¼Æ¦r&¼Æ¦r¸É¹s.rar (3.47 KB)

¦U¦ì¦n!
´£¨ú¼Æ¦r³¡¤À, §Ú¥u·|³Ì°ò¥»ªº¥ÎFIND/LEN/MID/RIGHT ºCºCªº, ±N "-" «e«áªº¼Æ¦r§ä¥X¨Ó,
³Ì«á¤@²Õ "-"«áªº¼Æ¦r¦A¥[0, µM«á¦A¥Î " & " ±N 11001 & 023 ¦ê°_¨Ó¡C

¦ý¨ç¼Æ´NÅܪº¦nªø, ·Q¾Ç²ß¬O§_¦³§ó§Ö§ó²³æªº¨ç¼Æ¥i¥H°µ¨ì©O?  ·PÁ¦U¦ì!

­q³æ½s¸¹                                  ´£¨ú¼Æ¦r©M³Ì«á¤@²Õ¼Æ¦rÅÜ3½X
VC-11001-23                           11001023
VC-11001-123                           11001123
       
¥u¨ú¥X«á­±¼Æ¦r³¡¤À11001 23, ³Ì«áªº23 ­nÅܦ¨3½X, 23 -->023

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-7-3 10:38 ½s¿è

¦^´_ 1# yc1031


  =SUBSTITUTE(MID(A1,FIND("-",A1)+1,255),"-",IF(LEN(A1)>11,"","0"))
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh



ÁÂÁ±z! ¾Ç²ß¤F~
­ì¨Ó¤£»Ý­n§Ë¦¨¨º»ò½ÆÂø...

TOP

=IF(LEN(A2)=11,MID(A2,4,5)&"0"&MID(A2,10,2),MID(A2,4,5)&MID(A2,10,3))

TOP

¦^´_ 4# isqueen


    ·PÁ±z!
±zªº¤èªk§Ú¸Õ¤F, ¤]¥i¦æ, ¥B¤]²¼ä,   ¾Ç²ß¤F:)

TOP

=MID(A1,4,5)&IF(VALUE(RIGHT(A1,3))<0,"0"&RIGHT(A1,2))

¸ê®Æ­Y¦³¨ä©T©wªº½s½X­ì«h~ °ò¥»¤W¨ú¥k¨ú¥ª³£¥i¥H³B²z~
¾Ç²ß¤~¯à´£¤É¦Û¤v

TOP

¦^´_ 6# hugh0620


½s½Xªº³Ì«á¦³¥i¯à¬O2½X©Î3½X
±z«Øijªº¨ç¼Æ, §Ú¦³¾ÇµÛ­×§ï¤@ÂIÂI¦¨ =MID(A2,4,5)&IF(VALUE(RIGHT(A2,3))<0,"0"&RIGHT(A2,2),RIGHT(A2,3))
¤]¬O¥i¦æªº! «D±`ÁÂÁ±z!

TOP

¥»©«³Ì«á¥Ñ Qin ©ó 2016-11-26 21:41 ½s¿è

¦p¦ó±N "-" ²Å¸¹§ï¦¨"0",

¨C²Õ½s¸¹¦@15­Ó¦r¤¸

¦p¤U:

1-1234
11-532
8-1
4-32167
M-1000

Åܦ¨
100000000001234
110000000000532
800000000000001
400000000032167
M00000000001000

TOP

A1 =SUM(MID(A1,{4,10},{5,3})*{1000,1})
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2016-11-27 06:45 ½s¿è

¦^´_ 8# Qin

¦^ÂÐ 8¼Ó·sÃD¥Ø
±N "-" ³B´¡¤J0¡Aªø«×§ï¬°15¦ì

=REPLACE(A1,FIND("-",A1),1,REPT(0,16-LEN(A1)))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : °µ¸Ó°µªº¨Æ¬O´¼¼z¡A°µ¤£¸Ó°µªº¨Æ¬O·Mè¡C
ªð¦^¦Cªí ¤W¤@¥DÃD