[µ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

Andy2483

¦^´_ 24# Andy2483


    ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
¤µ¤Ñ½Æ²ß¦¹ÃD,µo²{Âöº|»PÂØ­z,¤ß±oµù¸Ñ¦p¤U

Option Explicit
Sub TEST_1()
Dim Brr, Crr, Y, T$, Z$, j%, V%, i&
'¡ô«Å§iÅܼÆ:(Brr,Crr,Y)¬O³q¥Î«¬ÅܼÆ,(T,Z)¬O¦r¦êÅܼÆ,
'(j,V)¬Oµu¾ã¼ÆÅܼÆ,i¬Oªø¾ã¼ÆÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY³o³q¥Î«¬ÅܼƬO ¦r¨å
Brr = Range([B2], Cells(Rows.Count, "A").End(3))
'¡ô¥OBrr¬O¤Gºû°}¦C,¥H[B2]¨ì AÄæ³Ì«á¦³¤º®eÀx¦s®æ,³o¨â®æ¤§¶¡©Ò¦³Àx¦s®æ­È±a¤J
Crr = Range([E2], Cells(Rows.Count, "E").End(3))
'¡ô¥OBrr¬O¤Gºû°}¦C,¥H[E2]¨ì EÄæ³Ì«á¦³¤º®eÀx¦s®æ,³o¨â®æ¤§¶¡©Ò¦³Àx¦s®æ­È±a¤J
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q1 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   T = Trim(Brr(i, 1)): If T = "" Then GoTo i01
   '¡ô¥OT³o¦r¦êÅܼƬOi°j°é¦C²Ä1ÄæBrr°}¦C­È¥h°£«e«áªÅ¥Õ¦r¤¸ÅSªº·s¦r:
   '¦pªGTÅܼƬOªÅ¦r¤¸!´N¸õ¨ì i01¦ì¸mÄ~Äò°õ¦æ
   V = Len(T)
   '¡ô¥OV³oµu¾ã¼ÆÅܼƬO TÅܼƪº¦r¤¸¼Æ
   For j = V To 1 Step -1
   '¡ô³]¶¶°j°é!j±qVÅܼƨì 1,¥O¨C­Ó°j°éj³£­n-1
      If Mid(T, j, 1) <> "0" Then
      '¡ô¦pªGTÅܼƱqjÅܼƶ}©l¨ú1­Ó¦rªº¦r¤¸¤£¬O"0"
         Z = Mid(T, j + 1)
         '¡ô¥OZ³o¦r¦êÅܼƬO TÅܼƱqj+1­Ó¦r¶}©l¨ì ³Ì«á¦r¤§¶¡ªº¦r¦ê
         Exit For
         '¡ô¸õ¥Xj°j°é
      End If
   Next
   T = Replace(UCase(Trim(Brr(i, 1))), "0", "") & Z
   '¡ô¥OTÅܼƬO i°j°é¦C²Ä1ÄæBrr°}¦C­È ¥h°£«e«áªÅ¥Õ¦r¤¸,¸gÂà¤Æ­^¤å¦r¥À¬°¤j¼g,
   '¦A±N0¸m´«¬°ªÅ¦r¤¸,³Ì«á³s±µZÅÜ¼Æ ²Õ¦¨·sªº¦r¦êÅܼÆ
   If Y.Exists(T) = Empty Then
   '¡ô¦pªG¥HTÅܼƬdY¦r¨å¸Ì¨S¦³³okey?
      Y(T) = Brr(i, 2)
      '¡ô¥O¥HTÅܼƷíkey,Item¬Oi°j°é¦C²Ä2ÄæBrr°}¦C­È
      ElseIf Y(T) <> Brr(i, 2) Then
      '¡ô§_«h¦pªG¥HTÅܼƬdY¦r¨å©Ò¦^¶Ç­È¤£¦P©ó i°j°é¦C²Ä2ÄæBrr°}¦C­È??
         MsgBox Brr(i, 1) & " ¥h0²¤Æ«áªº¸ê®ÆÄ榳¦P½s¸¹¤£¦P°Ó«~ºÃ¼{"
         '¡ô¸õ¥X´£¥Üµ¡~~
         Exit Sub
         '¡ôµ²§ôµ{¦¡°õ¦æ
   End If
i01:
Next
For i = 1 To UBound(Crr)
'¡ô³]¶¶°j°é!i±q1¨ì Crr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   T = Trim(Crr(i, 1)): If T = "" Then GoTo i02
   '¡ô¥OTÅܼƬO i°j°é¦C²Ä1Äæ°}¦C­È¥h°£«e«áªÅ¥Õ¦r¤¸«áªº·s¦r¦ê,
   '¦pªGTÅܼƬOªÅ¦r¤¸!´N¸õ¨ì i02¦ì¸mÄ~Äò°õ¦æ
   V = Len(T)
   '¡ô¥OV³oµu¾ã¼ÆÅܼƬO TÅܼƪº¦r¤¸¼Æ
   For j = V To 1 Step -1
   '¡ô³]¶¶°j°é!j±qVÅܼƨì 1,¥O¨C­Ó°j°éj³£­n-1
      If Mid(T, j, 1) <> "0" Then
      '¡ô¦pªGTÅܼƱqjÅܼƶ}©l¨ú1­Ó¦rªº¦r¤¸¤£¬O"0"
         Z = Mid(T, j + 1)
         '¡ô¥OZ³o¦r¦êÅܼƬO TÅܼƱqj+1­Ó¦r¶}©l¨ì ³Ì«á¦r¤§¶¡ªº¦r¦ê
         Exit For
         '¡ô¸õ¥Xj°j°é
      End If
   Next
   T = Replace(UCase(Trim(Crr(i, 1))), "0", "") & Z
   '¡ô¥OTÅܼƬO i°j°é¦C²Ä1ÄæBrr°}¦C­È ¥h°£«e«áªÅ¥Õ¦r¤¸,¸gÂà¤Æ­^¤å¦r¥À¬°¤j¼g,
   '¦A±N0¸m´«¬°ªÅ¦r¤¸,³Ì«á³s±µZÅÜ¼Æ ²Õ¦¨·sªº¦r¦êÅܼÆ
   Crr(i, 1) = Y(T)
   '¡ô¥Oi°j°é¦C²Ä1ÄæCrr°}¦C­È¬O ¥HTÅܼƬdY¦r¨å©Ò¦^¶ÇªºItem­È
i02:
Next
[I2].Resize(UBound(Crr), 1) = Crr
'¡ô¥O[I2]ÂX®i¦V¤UCrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹¼Æ ªºÀx¦s®æ½d³ò,¥HCrr°}¦C­È±a¤J
Erase Brr, Brr: Set Y = Nothing
'¡ôÄÀ©ñÅܼÆ
End Sub

Andy2483

¦^´_ 19# ML089
¦^´_ 1# cdkee


    ½×¾Â¨ì³B³£¦³Ä_ÂÃ,ÁÂÁ½׾Â,ÁÂÁ«e½ú
«á¾Ç½m²ß¦r¨å»P°}¦Cªº¤èªk,½Ð«e½ú¦A«ü¾É

°õ¦æ«e:

¤U¸ü (127.12 KB)

2023-3-10 15:31

°õ¦æµ²ªG:

¤U¸ü (131.48 KB)

2023-3-10 15:33

Option Explicit
Sub TEST_1()
Dim Brr, Crr, i&, T$, V%, Y, Z$, j%
Set Y = CreateObject("Scripting.Dictionary")
Brr = Range([B2], Cells(Rows.Count, "A").End(3))
Crr = Range([E2], Cells(Rows.Count, "E").End(3))
For i = 1 To UBound(Brr)
   T = Trim(Brr(i, 1)): If T = "" Then GoTo i01
   V = Len(T)
   For j = V To 1 Step -1
      If Mid(T, j, 1) <> "0" Then
         Z = Mid(T, j + 1): T = Mid(T, 1, j)
         Exit For
      End If
   Next
   T = Replace(UCase(Trim(Brr(i, 1))), "0", "") & Z
   If Y.Exists(T) = Empty Then
      Y(T) = Brr(i, 2)
      ElseIf Y(T) <> Brr(i, 2) Then
         MsgBox Brr(i, 1) & " ¥h0²¤Æ«áªº¸ê®ÆÄ榳¦P½s¸¹¤£¦P°Ó«~ºÃ¼{"
         Exit Sub
   End If
i01:
Next
For i = 1 To UBound(Crr)
   T = Trim(Crr(i, 1)): If T = "" Then GoTo i02
   V = Len(T)
   For j = V To 1 Step -1
      If Mid(T, j, 1) <> "0" Then
         Z = Mid(T, j + 1): T = Mid(T, 1, j)
         Exit For
      End If
   Next
   T = Replace(UCase(Trim(Crr(i, 1))), "0", "") & Z
   Crr(i, 1) = Y(T)
i02:
Next
[I2].Resize(UBound(Crr), 1) = Crr
Erase Brr, Brr: Set Y = Nothing
End Sub

Qin

¦^´_ 22# ML089

.ÁÂÁÂ!

¨ü±Ð¤F.

ML089

¦^´_ 20# Qin

¥[¤WIF§PÂ_ DÄæ¬O§_¦³­È¡AµL­È®É¬°ªÅ¥Õ

{ =IF(D2="","",VLOOKUP(SUBSTITUTE(D2,"-","*"),IF({1,0},A$2:A$99&"",B$2:B$99),2,)) }

AÄæ¸ê®Æ¬° Table_Array ªº Index-key ³Ì¦n¯à¬O©T©wªø«×¡A²Î¤@¬°¤å¦r¡A¹ï¬d¸ß¤ñ¸û®e©ö¡C
¥Ø«e AÄæ ¦³¤å¦r¡B¼Æ¦r«¬ºA¡AIndex-keyªøµu¤£¤@­P¡A
ÁöµM ¥i¥H¥Î  «e¦r¦ê + "*" + «á¦r¦ê ¨Ó¬d¸ß¡A¦ý¤]¦³¥i¯à·|¦³»~§P±¡ªp

¨Ò¦p
A1*123¬d¸ß

¤U¦C³£¥i¥H²Å¦X¡A·|Àu¥ý¿ï¨ú²Ä¤@­Ó
A10000123
A12000123
A1A123123

Hsieh

¦^´_ 8# Qin

=SUBSTITUTE(A1,"-",REPT(0,15-LEN(A1)+1))

Qin

¦^´_ 19# ML089

ÁÂÁÂ, ¥i¥H¤F

¦A´£°Ý

¦pªGDÄæ©|¥¼¿é¤J¥ô¦ó¤å¼Æ¦r

F2ªº°}¦C¤w¤U©Ô¤½¦¡¤£¬O"0", ¦Ó¬OªÅ¥Õªº.

³o¤S­n¦p¦ó¤U¤½¦¡?

ML089

¦^´_ 18# Qin

F2 =VLOOKUP(SUBSTITUTE(D2,"-","*"),IF({1,0},A$2:A$99&"",B$2:B$99),2,)
°}¦C¤½¦¡¡A»Ý¥ÎCTRL+SHIFT+ENTER¤TÁä¿é¤J¤½¦¡


Test_2.rar (8.54 KB)

¤U¸ü¦¸¼Æ: 3

2017-3-22 23:09

Qin

¦^´_ 17# ML089

·Q°Ýªº¬OAÄ檺½s½X¤å¼Æ¦rªøµu¤£¤@
EÄæ¬O­n¦p¦ó¦bDÄæ¿é¤J«á,¦p¦ó±N²Å¸¹"-"¦Û°Ê¸É¹s?

ML089

¦^´_ 16# Qin

¨S¦³¤Ç°t¦p¤U¡Aªø«×¤£¼Ë

10000000000001a
100000000000001a

150000000002160
15002160

15gb00000000435
15GB0435


20000000000336a
200000000000336a