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

yc1031

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

¤U¸ü¦¸¼Æ: 16

2012-7-3 08:18

¦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

¦^´_ 1# yc1031


  =SUBSTITUTE(MID(A1,FIND("-",A1)+1,255),"-",IF(LEN(A1)>11,"","0"))

yc1031

¦^´_ 2# Hsieh



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

isqueen

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

yc1031

¦^´_ 4# isqueen


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

hugh0620

=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~

yc1031

¦^´_ 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!

Qin

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

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

¦p¤U:

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

Åܦ¨
100000000001234
110000000000532
800000000000001
400000000032167
M00000000001000

ML089

A1 =SUM(MID(A1,{4,10},{5,3})*{1000,1})

ML089

¦^´_ 8# Qin

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

=REPLACE(A1,FIND("-",A1),1,REPT(0,16-LEN(A1)))