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

¦p¦óºI¨ú¬Y¤@¦r²´(K2)«_«á¤§«áªº10­Ó¦r

¦p¦óºI¨ú¬Y¤@¦r²´(K2)«_«á¤§«áªº10­Ó¦r

°ÝÃD:
¦p¦óºI¨úk2«_¸¹¥H«áªº¦r¦ê¡A¦@10­Ó¦r(¤£§t«_¸¹)¡Aµª®×¦bBÄæ
k2®æ¦¡¡A­^¤å¦rk¦³¤j¼g©Î¤p¼g¡F¥t¡Ak2«á­±«_¸¹¡A¦³ªº¥þ§Î¦³ªº¥b§Î
°£¤F¥H¤Wªº±¡§Î¥~¡A¦³¥i¯à¦b2«á­±¦³¤@­Ó¡y½X¡z¦r(¨Ò¦pA5~A8)

¦p¦óºI¨ú¬Y¤@¦r²´(K2)«_«á¤§«áªº10­Ó¦r.zip (6.74 KB)

joyce

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-28 08:56 ½s¿è

¦^´_ 7# leiru


    ÁÂÁ«e½ú
¥H¤UÅܧó±ø¥ó°õ¦æµ²ªG»P¤ß±oµù¸Ñ,½Ð°Ñ¦Ò

°õ¦æ«e:
°õ¦æ«e.jpg
2022-12-28 08:42


°õ¦æµ²ªG:
°õ¦æµ²ªG.jpg
2022-12-28 08:43


Option Explicit
Sub TEST_20221228_2()
Dim i&, N&, A$, Arr, Brr, j
'¡ô«Å§iÅܼÆ:(i,N)¬Oªø¾ã¼ÆÅܼÆ,A¬O¦r¦êÅܼÆ,(Arr,Brr,j)¬O³q¥Î«¬ÅܼÆ
Arr = Range([A1], Cells(Rows.Count, 1).End(3))
'¡ô¥OArr¬O ¤Gºû°}¦C,¥H[A1]¨ìAÄ榳¤º®e³Ì«á¦CÀx¦s®æ,³o¤§¶¡Àx¦s®æ­È­Ë¤J
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é!i±q1¨ìArr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ
   A = StrConv(Arr(i, 1), vbNarrow)
   '¡ô¥OA³o¦r¦êÅܼƬOi°j°éArr°}¦C­È¸gStrConv() ±N¥þ§Î¦rÂର¥b§Î¦r ªº¦r¦ê
   'StrConv ¨ç¦¡
   'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/strconv-function

   If A Like "*:7????????H*" Then
   '¡ô¦pªGA¦r¦êÅܼƥ]§t ":"«_¸¹«á­±¦³10­Ó¥H¤W¦r¤¸ ?
      Brr = Split(A, ":")
      '¡ô¥OBrr¬O¤@ºû°}¦C,±a¤J¥H":"¤À³ÎA¦r¦êÅܼƪº°}¦C­È
      For Each j In Brr
      '¡ô³]¶¶°j°é!¥Oj¬O Brr°}¦C¸Ìªº­È
         j = Left(j, 10)
         '¡ô¥Oj³o³q¥ÎÅܼƬO¦Û¨­ ¨ú«e10­Ó¦rªº¦r¦ê
         If j Like "7????????H" Then Arr(i, 1) = j: Exit For
         '¡ô¦pªGj¦r¦êÅܼƬO «e7 «áH ¥]§¨ªº10­Ó¦r¤¸,´N¥Oi°j°éArr°}¦C­È´«¦¨jÅܼƭÈ:µ²§ôj°j°é
      Next
      If Arr(i, 1) Like "7GA??????H" = False Then N = N + 1
      '¡ô¦pªGi°j°éArr°}¦C­È¤£¬O«e7GA «áH ¥]§¨ªº10­Ó¦r¤¸,´N¥ON³oªø¾ã¼ÆÅܼƲ֥[1
      Else
         Arr(i, 1) = ""
         '¡ô¥Oi°j°éArr°}¦C­È¬O ªÅ¦r¤¸
   End If
Next
[C1].Resize(UBound(Arr), 1) = Arr
'¡ô¥O[C1] ÂX®i¦V¤UArr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ¦C, ¦V¥k¤£ÂX®i, ³o½d³òÀx¦s®æ­È¥HArr°}¦C­È±a¤J
MsgBox N & " ­Óµ²ªGºÃ¦ü³W«h²§±`!"
Set Arr = Nothing
End Sub

#12¼Ó½d¨Ò:
[E1].Resize(UBound(Arr), 1) = Arr:
2022-12-28_085235.JPG
2022-12-28 08:53
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 1# leiru


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«²ß±o¦h¼Ëª¾ÃÑ»P¸gÅç,¥H¤U¤ß±oµù¸Ñ½Ð°Ñ¦Ò
°õ¦æ«e:
°õ¦æ«e.jpg
2022-12-28 08:10


°õ¦æµ²ªG:
µ²ªG.jpg
2022-12-28 08:10


Option Explicit
Sub TEST_20221228_1()
Dim i&, N&, A$, Arr
'¡ô«Å§iÅܼÆ:(i,N)¬Oªø¾ã¼ÆÅܼÆ,A¬O¦r¦êÅܼÆ,Arr¬O³q¥Î«¬ÅܼÆ
Arr = Range([A1], Cells(Rows.Count, 1).End(3))
'¡ô¥OArr¬O ¤Gºû°}¦C,¥H[A1]¨ìAÄ榳¤º®e³Ì«á¦CÀx¦s®æ,³o¤§¶¡Àx¦s®æ­È­Ë¤J
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é!i±q1¨ìArr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ
   A = StrConv(Arr(i, 1), vbNarrow)
   '¡ô¥OA³o¦r¦êÅܼƬOi°j°éArr°}¦C­È¸gStrConv() ±N¥þ§Î¦rÂର¥b§Î¦r ªº¦r¦ê
   'StrConv ¨ç¦¡
   'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/strconv-function

   If A Like "*:??????????*" Then
   '¡ô¦pªGA¦r¦êÅܼƥ]§t ":"«_¸¹«á­±¦³10­Ó¥H¤W¦r¤¸ ?
      Arr(i, 1) = Left(Split(A, ":")(1), 10)
      '¡ô¥Oi°j°éArr°}¦C­È´«¦¨: A¦r¦êÅܼƥH ":"¦r¤¸¤À³Î«áªº¯Á¤Þ¸¹1ªº¤@ºû°}¦C­È¦A¨ú¥ª°¼10¦r¤¸
      If Arr(i, 1) Like "7????????H" = False Then N = N + 1
      '¡ô¦pªGi°j°éArr°}¦C­È¤£¬O«e7 «áH ¥]§¨ªº10­Ó¦r¤¸,´N¥ON³oªø¾ã¼ÆÅܼƲ֥[1
      Else
         Arr(i, 1) = ""
         '¡ô¥Oi°j°éArr°}¦C­È¬O ªÅ¦r¤¸
   End If
Next
[C1].Resize(UBound(Arr), 1) = Arr
'¡ô¥O[C1] ÂX®i¦V¤UArr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ¦C, ¦V¥k¤£ÂX®i, ³o½d³òÀx¦s®æ­È¥HArr°}¦C­È±a¤J
MsgBox N & " ­Óµ²ªGºÃ¦ü³W«h²§±`!"
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

­Y¨Ï¥Î­ã¤jªº¤è¦¡¡A±z«á­±{3,4}ªº°}¦C­n»P·s¼W±ø¥ó"»²¤G:"«áªº­Ó¼Æ¬ÛºÙ¤~·|¥¿½T¡C¦p¤U:
­×§ï«e=MID(A2,-LOOKUP(,-SEARCH({"K2:","K2½X:","»²¤G:"},ASC(A2))-{3,4}),10)
­×§ï«á=MID(A2,-LOOKUP(,-SEARCH({"K2:","K2½X:","»²¤G:"},ASC(A2))-{3,4,3}),10)

TOP

¦^´_ 14# ML089


    ÁÂÁ±z¦^ÂÐ,Àx¦s®æ¤º¦³¦h­Ó«_¸¹
joyce

TOP

­Y¥u¦³¤@­Ó ":" ©Î "¡G"
=MID(A2,SEARCH(":",ASC(A2))+1,10)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 11# leiru


    =MID(A2,FIND(":",ASC(A2),-LOOKUP(,-SEARCH({"K2","»²¤G"},A2)))+1,10)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 11# leiru

¦p¦óºI¨ú¬Y¤@¦r²´(K2)«_«á¤§«áªº10­Ó¦r.zip (7.51 KB)

joyce

TOP

¦A½Ð±Ð,°w¹ï³oÃD,¦AÅܤÆÃD¥Ø,¦p¤U:
°ÝÃD:
¦p¦óºI¨úk2«_¸¹¥H«áªº¦r¦ê¡A¦@10­Ó¦r(¤£§t«_¸¹)¡Aµª®×¦bBÄæ
k2®æ¦¡¡A­^¤å¦rk¦³¤j¼g©Î¤p¼g¡F¥t¡Ak2«á­±«_¸¹¡A¦³ªº¥þ§Î¦³ªº¥b§Î
°£¤F¥H¤Wªº±¡§Î¥~¡A¦³¥i¯à¦b2«á­±¦³¤@­Ó¡y½X¡z¦r(¨Ò¦pA5~A8)
¥t¥~¡A¼W¥[¤F¡AÀx¦s®æ¤º¦³(»²¤G:©Î»²¤G¡G)³o­Ó¦r²´¡A¤]­n«_¸¹«á­±10­Ó¦r(¨Ò¦pA10~A12)
joyce

TOP

=MID(A1,FIND(":",ASC(A1),SEARCH("K2",A1))+1,10)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD