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

[µo°Ý] Â^¨ú³¡¤À¦r¤¸

[µo°Ý] Â^¨ú³¡¤À¦r¤¸

¦pªG§Ú¦bA1Ä榳¡G¾Ç®Õ¦a§}¡G407 ¥x¤¤¥«¦è¤Ù°Ï¤åµØ¸ô100¸¹
§Ú­n¦p¦ó¦bB1Äæ¨ú¥X"407"¡BC1Äæ¨ú"¥X¥x¤¤¥«¦è¤Ù°Ï"
ÁÂÁ¡I

¦^´_ 1# shootingstar

°ò¥»¤W´N¬O¥Îmid¨ç¼Æ¡A¤£¹L­n¬Ý¸ê®Æªº®æ¦¡
¦pªG¥þ³¡ªº®æ¦¡³£¬O"¾Ç®Õ¦a§}¡Gxxx xxxxx°Ïxxxxxxxxx"ªº¸Ü´N¥i¥Î
B1=mid(A1,6,3)
C1=mid(A1,10,6)
¦pªG®æ¦¡¦r¼Æ¤£¤@¼Ë¡A©Î¬O·Q­n§ìªºªF¦è¤£¤@¼Ë(¶l»¼°Ï¸¹¬O§_³£¬O¤T½X¡H°Ï°ì­n§ì¨ì¶m¡HÂí¡H°Ï¡H)¡A´N¥²»Ý¥t¥~³]­p
¥i°Ñ¦Ò
http://office.microsoft.com/zh-tw/excel-help/HA010102341.aspx

TOP

¦^´_ 2# Bodhidharma


    ÁÂÁÂBodhidharma ªº¦^µª¡A§Ú¤]¬O¥ÎMID¨ç¼Æ¨ú±o§Ú­nªº¸ê°T¡A¥H¤U¬O§Ú¦Û¤v¼gªº¨ç¼Æ¡A¦³ÂI¤¾ªø¡A¤£¹L¬ù8¦¨¥H¤W¥i¥H¹F¦¨¥Ø¼Ð
    B1=MID(A1,FIND("¡G",A1)+1,3)
    C1==MID(A1,MAX(IF(ISERR(FIND({"¿¤","¥«"},A1)),0,FIND({"¿¤","¥«"},A1)))-2,3)&MID(A1,MAX(IF(ISERR(FIND({"¶m","Âí","¥«","°Ï","§ø"},A1)),0,FIND({"¶m","Âí","¥«","°Ï","§ø"},A)))-2,3)
   C1ªº¨ç¼Æ´X¥G¥i¥H¹F¦¨§Úªº»Ý¨D¡A¥i¬O¦pªG§ÚªºA1Äæ¬O¥u¦³"¾Ç®Õ¦a§}¡G"©Î¬O"¾Ç®Õ¦a§}¡G407 ¥x¤¤¥«¤åµØ¸ô100¸¹"´N·|¥X²{#VALUE!ªº¿ù»~°T®§¡A¦³ªk¥i¸Ñ¶Ü¡HÁÂÁ¡C

TOP

¦^´_  Bodhidharma


    ÁÂÁÂBodhidharma ªº¦^µª¡A§Ú¤]¬O¥ÎMID¨ç¼Æ¨ú±o§Ú­nªº¸ê°T¡A¥H¤U¬O§Ú¦Û¤v¼gªº¨ç ...
shootingstar µoªí©ó 2013-4-3 15:47


¥Î§Aªº¤½¦¡¸Õ¤F¤@¨Ç¦a§}¡G
¾Ç®Õ¦a§}¡G407 ¥x¤¤¥«¦è¤Ù°Ï¤åµØ¸ô100¸¹        407        ¥x¤¤¥«¦è¤Ù°Ï
¾Ç®Õ¦a§}¡G407 ¥x¤¤¥«¤åµØ¸ô100¸¹        407        ¥x¤¤¥«¥x¤¤¥«
¾Ç®Õ¦a§}¡G                #VALUE!
¾Ç®Õ¦a§}¡G123 ®ç¶é¿¤¤¤Ãc¥«xx¸ôxx¸¹        123        ¤¤Ãc¥«¤¤Ãc¥«

¦³´X­Ó°ÝÃD¡G
#VALUE!³¡¤À¡G
±q¡u¤½¦¡¡÷µû¦ô­È¤½¦¡¡v¤¤°lÂÜ¡A¥i¥H¬Ý¥X¦]¬°¡u¿¤¡v©M¡u¥«¡v³£§ä¤£¨ì¡A¦]¦¹¦^¶Ç0¡A0ÁÙ­n´î2¥X²{­t­È´N·|¦³°ÝÃD¡A¸Ñ¨M¤èªk¥i¥H¦A¥[¤@­Óiserror¡Gif(iserror(­ì¤½¦¡),"",­ì¤½¦¡)
note: excel2007«á¦³iferror¤½¦¡¡A¥i¥H¥Îiferror(­ì¤½¦¡,"")§Y¥i
¡u¥«¡v­«½Æ¥X²{ªº°ÝÃD¡G
¦]¬°¦³®É­Ô¥«¬O¦b«e­±(¦p¡G·s¥_¥«xxx)¡A¦³®É­Ô¦b«á­±(¦p¡G®ç¶é¿¤¤¤Ãc¥«)¥X²{
¦]¦¹¦³¥X²{¥«ªº®É­Ô¡A¤½¦¡´N·|¥X°ÝÃD(­«½Æ§ä)
¸Ñ¨M¤èªkÀ³¸Ó«Ü¦hºØ
¤ñ¤è»¡
­Y"¿¤"¦s¦b,¬d¶mÂí¥«°Ï§ø
­Y"¥«"¦s¦b¡A¬d¶mÂí°Ï§ø

TOP

¦^´_ 3# shootingstar
¸Õ¸ÕªþÀÉ
¦a§}Â^¨ú.rar (7.06 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# Hsieh


    ÁÂÁÂHsiehªº¦^ÂСA¦pªG¨S¦³B1¶l»¼°Ï¸¹³o¤@Äæ¡A¤½¦¡­n¦p¦ó­×§ï¡HÁÂÁ¡C

TOP

¦^´_ 6# shootingstar
³q±`¤½¦¡¤¤°Ñ·Ó¨ìªº¦ì§}¡Aª½±µ±N¸Ó°Ñ·Ó¤½¦¡¨ú¥N§Y¥i
¦ý¬O¡A¦]¬°¤½¦¡ªø«×­­¨î·|³y¦¨µLªkª½±µ±N¤½¦¡±a¤J
³o®É´N±N¸Ó°Ñ·Óªº¤½¦¡¼g¦¨©w¸q¦WºÙ¨Ó±a¤J§Y¥i

¦a§}Â^¨ú.rar (7.28 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# shootingstar

¨ú°Ï°ì¸¹½X
B1 =IF(COUNT(-MID(A1,7,1)),-LOOKUP(,-MID(A1,6,{1,2,3})),"")

¨ú¦a§}«e¬q
C1 =SUBSTITUTE(MID(A1,6,-5-LOOKUP(,-FIND({"¿¤","¶m","Âí","¥«","°Ï","§ø","¨½"},A1))),B1,)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 8# ML089
¦a§}«eºó¦r¤¸¤£½T©wªø«×·|¥X¿ù

¦a§}.zip (5.21 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 9# Hsieh


    ¨ú¶l»¼°Ï¸¹¥H "¡G" ¬°¤À¬É
=IF(COUNT(-MID(A2,FIND("¡G",A2)+1,1)),-LOOKUP(,-MID(A2,FIND("¡G",A2)+1,{1,2,3})),"")

¨ú¦a§}«e¬q
°Ñ¦Ò ¶Wª©ªº¤½¦¡ ¬°¨Î
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤£©È¨Æ¦h¡A¥u©È¦h¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD