[µo°Ý] ¦p¦ó§Q¥Î¨ç¼Æ¦Û°Ê°µ¸ê®Æ­åªR

williampeng

¦U¦ì¦n

                   §Ú¦³¤@­Ó¤é±`­n°µªº¸ê®Æ¡A©ó¨t²Î§ì¤U®É¸ê®Æ³£¦b²Ä¤@­ÓÄæ¦ì¡A¥²¶·¨Ï¥Î¸ê®Æ­åªR¤~¯à¦A®³¨Ó°µ¤ñ¹ï

                  ¬O§_¯à¦³¨ç¼Æ¯à±N¸ê®Æ¦Û°Ê­åªR«á©ñ¤J¥t¤@­Ó¤u§@ªí¤¤?

                    ½Ð°Ñ¦Òªþ¥óÀÉ  Sample    ¨ä¤¤ "Raw data" ´N¬O¨t²Î§ì¤Uªº¸ê®Æ "¸ê®Æ²M³æ" ´N¬O§Æ±æ¾ã²z¦nªºª¬ªp

                    ½Ð°ª¤â«üÂI°g¬z   Sample.zip (5.2 KB)

½d¨Ò
¤U¸ü¦¸¼Æ: 13

2014-1-27 15:01

williampeng

´±°Ý°ª¤â­Ì¡A¦¹ÃDµL¸Ñ¶Ü?

aer

¦^´_ 1# williampeng

¸Õ¸Õ¦b¸ê®Æ²M³æ¤u§@ªíªºB3 ~ J3 ¤À§O¿é¤J¥H¤Uªº¤½¦¡
B3=TRIM(LEFT('Raw data'!A3,FIND("A",'Raw data'!A3,1)))
C3=TRIM(MID('Raw data'!A3,FIND("T0",'Raw data'!A3,1),4))
D3=TRIM(MID('Raw data'!A3,FIND("¤w³B",'Raw data'!A3,1),FIND("8P",'Raw data'!A3,1)-2-FIND("¤w³B",'Raw data'!A3,1)))
E3=TRIM(MID('Raw data'!A3,FIND("8P",'Raw data'!A3,1)-1,FIND("9100",'Raw data'!A3,1)-FIND("8P",'Raw data'!A3,1)-1))
F3=VALUE(MID('Raw data'!A3,FIND("9100",'Raw data'!A3,1),4))
G3=VALUE(MID('Raw data'!A3,FIND("9100",'Raw data'!A3,1)+4,(FIND("KG",'Raw data'!A3,1)-3-FIND("9100",'Raw data'!A3,1)-3)
H3=TRIM(MID('Raw data'!A3,FIND("KG",'Raw data'!A3,1),2))
I3=VALUE(MID('Raw data'!A3,FIND("0",'Raw data'!A3,116),1))
J3=VALUE(RIGHT('Raw data'!A3,1))
¦V¤U½Æ»s

ML089

B3
=IF(COLUMN(A1)<5,TRIM(INDEX(MID('Raw data'!$A3,{1,18,26,51},{15,8,25,30}),COLUMN(A1))),TRIM(MID(SUBSTITUTE(TRIM(MID('Raw data'!$A3,90,999))," ",REPT(" ",99)),(COLUMN(A1)-5)*99+1,99)))

¥k©Ô¤U©Ô
³£¬O¤å¦r®æ¦¡

williampeng

¦^´_ 4# ML089


           ÁÂÁª©¥D¡A¥i¥H¥Î¤F
     ¤½¦¡¦³ÂIÁ}²`¡A±o¦n¦n¬ã¨s¥H«á¤~¯à¬¡¥Î¦b¨ä¥L»Ý¨D¤W

                  ¶¶«K«ô­Ó±ß¦~¡A¯¬ºÖ¤j®a·s¦~§Ö¼Ö¡A¸U¨Æ¦p·N¡C

ML089

¦^´_ 5# williampeng

¤½¦¡¤À¬°2¬q¨Ó¬Ý¤ñ¸û®e©ö¡A
«e4­Ó±Ä¥Î©T©w¦ì¸m¨ú¥X¡A«á­±¥HªÅ®æ(³sÄòªÅ®æµø¬°1­Ó)»á¶}¨ú¥X
=IF(COLUMN(A1)<5,
TRIM(INDEX(MID('Raw data'!$A3,{1,18,26,51},{15,8,25,30}),COLUMN(A1))),
TRIM(MID(SUBSTITUTE(TRIM(MID('Raw data'!$A3,90,999))," ",REPT(" ",99)),(COLUMN(A1)-5)*99+1,99))
)