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

Excel ¨ú¯S®í¦r¤¸

Excel ¨ú¯S®í¦r¤¸

Dear ¤j¤j,
§Ú­n¨úÀx¦s®æ¥kÃä¶}©lµo²{¦³"+"¸¹«á­±ªº¦r¦ê

§Ú¤Uªº¤½¦¡
=MID(A2,FIND("+",A2)+1,4) --> ¦]¥¦§ì¨ì¤F²Ä¤@­Ó+¸¹¡A©Ò¥H´NÅܦ¨
3+L0000-29-ABC+PP    ---> L000


§Ú·Q­nÅܦ¨¤U¦Cµª®×
3+L0000-29-ABC+PP    ---> PP
3+A0000-30-CDE+PQQ     ---> PQQ

½Ð°Ý¤½¦¡­n«ç»ò­×­q¡H
Just do it.

¦^´_ 10# ML089
ML089 ¦­¦w¡I
·PÁ±z¹ï¤½¦¡¥Îªk¤W»¡©ú¡AÅý§Ú¾Ç²ß¨ì«Ü¦h¡C
Thanks
Just do it.

TOP

¦^´_ 1# jsc0518

§Ú­n¨úÀx¦s®æ¥kÃä¶}©lµo²{¦³"+"¸¹«á­±ªº¦r¦ê

§Ú¤Uªº¤½¦¡
=MID(A2,FIND("+",A2)+1,4) --> ¦]¥¦§ì¨ì¤F²Ä¤@­Ó+¸¹¡A©Ò¥H´NÅܦ¨
3+L0000-29-ABC+PP    ---> L000

§Ú·Q­nÅܦ¨¤U¦Cµª®×
3+L0000-29-ABC+PP    ---> PP
3+A0000-30-CDE+PQQ     ---> PQQ


----------------------
­Y¸ê®Æ¦³³W«h©Ê
§Aªº¤½¦¡¥u­n²³æ­×§ï´N¥i¥H¨Ï¥Î¡A­ã¤jªº¤½¦¡¬O¥i¥H¤ñ¶}¦h­Ó "+" §ä³Ì«á¤@­Ó¡A¼u©Ê¤ñ¸û¤j¡C

=MID(A2,FIND("+",A2)+1,4)
§ï¬°
=MID(A2,FIND("+",A2,3)+1,9)  

3 ¬O¥Ñ²Ä3¦r¤¸°_©¹«á´M§ä¡A§Aªº²Ä¤@­Ó "+" ¬O¦b¦r¦ê²Ä2¦ì¸m¡A©Ò¥H¥Ñ¦r¦ê²Ä3¦ì¸m©¹«á¬d¸ß²Ä¤G­Ó"+"¡A­Y¦³¥¼ª¾¼Æ­Ó "+" ¡A´N»Ý­n¥Î ­ã¤j ªº¤½¦¡¡C
9 ¥i¥H³]©w¬° + ¥H«á¥i¥H¨úªº³Ì¦h¦r¤¸¡A©Ò¥H¤]¥i¥H³]¬°99¡B999...
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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


­ã´£³¡ªL
·PÁ±zªº¦^ÂÐ
½Ð°Ý¤@¤U¡A³o¤@¬q»yªkªº·N«ä¡H
ROW($119)),1,)
jsc0518 µoªí©ó 2019-1-3 19:37

§A¨ú¿ù¤½¦¡ªº°Ï°ì¤F
¥i¥H¨Ï¥ÎF9Ū°Ï³¡¥÷¤½¦¡ªºµ²ªG¨Ó¤F¸Ñ¤½¦¡ªº·N¸q
¿ï¨úFIND("+",A2,ROW($1:$39))«á«öF9
·|¥X²{¤@­Ó°}¦C¡Afind¥[¤Wrow($1:$39)ªº·N«ä¬O±qA2¦r¦êªº²Ä1¡B²Ä2¡B²Ä3...¡B²Ä39¦ì¸m¶}©l§ä+³o­Ó²Å¸¹¨Ã¦^¶Ç39­Óµ²ªG
­Y§Aªº¦r¦ê©ó²Ä39­Ó¦r¤¸«áÁÙ¦³¥X²{+ªº®É­Ô¡A´N»Ý­×§ï¬°$1:$50©Î§ó¤j

§Úı±olookup¤ñ¸ûÃø²z¸Ñ¡A­Y¸ÑÄÀ¿ù»~½Ð¨ó§U«ü¥¿
lookup·|¦^¶Ç¤p©ó©Îµ¥©ólookup_valueªº³Ì¤j­È
¦¹¤½¦¡§ï¬°MID(A2,LOOKUP(100,FIND("+",A2,ROW($1:$39))),9)
©ó¦¹¨Ò¤¤¥i±o¨ì¬Û¦Pªºµ²ªG

¥¼«ü©wlookup_value¡A¬G¶Ç¦^array¤¤²Å¦X±ø¥óªº³Ì«á¤@­Ó
¦ý¦^¶Çªº15¦bEXCEL¹w³]¬°³q¥Î®æ¦¡¨Ã«D¼Æ¦r¡A¨ç¦¡µLªk­pºâ
¬G¦bFIND«e¤è¥[¤W-¡A±j¨î§ä¨ìªºµ²ªGÅܦ¨¼Æ¦r¡A¦ý¤]Åܦ¨¤F­t¼Æ
¬Glookup»Ý¦A¥[-¡AÂର¥¿¼Æ

TOP

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


­ã´£³¡ªL
·PÁ±zªº¦^ÂÐ
½Ð°Ý¤@¤U¡A³o¤@¬q»yªkªº·N«ä¡H
ROW($1:$19)),1,)
Just do it.

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2019-1-2 18:46 ½s¿è

¦^´_ 6# jsc0518


¨º¬O³Ì²³æ¤S¦³®Ä²vªº¤èªk,
¤]¥i¸Õ¸Õ³o­Ó
=VLOOKUP("+*",RIGHT(A2,ROW($1:$19)),1,)  °}¦C¤½¦¡(¤TÁä¿é¤J)

©Î/¤@¯ë¤½¦¡:
=MID(A2,-LOOKUP(,-FIND("+",A2,ROW($1:$39))),9)

TOP

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

Dear ­ã´£³¡ªL

­Y§Ú­n§ì¥kÃä¶}©lµo²{¦³"+"¸¹«á­±ªº¦r¦ê (§t¥[¸¹)
§Ú¦Û¤v¬O¥Î¤U¦C»yªk
3+L0000-29-ABC+PP    ---> +PP
="+"&TRIM(RIGHT(SUBSTITUTE(A2,"+",REPT(" ",9)),9))

¬O§_¦³¨ä¥Lªº¤èªk¡H
Just do it.

TOP

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

­ã´£³¡ªL

ÁÂÁ±zªº±Ð¾É¼Ú¡I
Just do it.

TOP

¦^´_ 3# jsc0518

=SUBSTITUTE(A2,"+",REPT(" ",9))
>> "3         L0000-29-ABC         PP"

"+"¥þÅܦ¨9­ÓªÅ¥Õ¦r¤¸, (­Y¦r¦êªø, ¥i§ï¦¨99)
¥k¨ú9­Ó¦r¤¸, ¦Atrim¥hªÅ¥Õ

TOP

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

­ã´£³¡ªL¡A¦­¦w
»yªk¥i¥H¥Î

¬O§_¥i¥HÀ°¦£»¡©ú»yªkªº·N«ä©O¡H
=TRIM(RIGHT(SUBSTITUTE(A2,"+",REPT(" ",9)),9))



§Ú¥uª¾¹D¡A¥h°£¦r¦êªÅ¥Õ¨ç¼Æ¡GTRIM¡BSUBSTITUTE
ÁÂÁ±z¡I
Just do it.

TOP

        ÀR«ä¦Û¦b : ¯àµ½¥Î®É¶¡ªº¤H¡A¥²¯à´x´¤¦Û¤v§V¤Oªº¤è¦V¡C
ªð¦^¦Cªí ¤W¤@¥DÃD