¨ú¥X©T©w¦r¦ê«áªº¼Æ¦r

leiru

°ÝÃD¡G
¦p¦ó¨ú¥XAÄ椺fee«áªº¼Æ¦r¡A­Y¦P¤@Àx¦s®æ¦³¨â­Ó¼Æ¦r¡A«h¤À§O¦C¥X
µª®×¦pB2¦ÜC3

ikboy

1. Sub zz()
   
2. Dim a, b(), k, n&
   
3. a = [a2:a3].Value
   
4. ReDim b(1 To UBound(a), 1 To 10)
   
5. With CreateObject("vbscript.regexp")
   
6.     .Pattern = ".*?fee\$(\d+)"
   
7.     .Global = True
   
8.     .ignorecase = True
   
9.     For i = 1 To UBound(a)
   
10.         If .test(a(i, 1)) Then
    
11.             k = Split(.Replace(a(i, 1), "$1|"), "|")
    
12.             n = IIf(UBound(k) > n, UBound(k), n)
    
13.             For j = 0 To UBound(k) - 1
    
14.                 b(i, j + 1) = k(j)
    
15.             Next
    
16.         End If
    
17.     Next
    
18.     [b2].Resize(i - 1, n) = b
    
19. End With
    
20. End Sub

½Æ»s¥N½X

­ã´£³¡ªL

B2:¥k©Ô/¤U©Ô
=iferror(-LOOKUP(1,-MID($A2,FIND("|",SUBSTITUTE($A2,"fee","|",COLUMN(A1)))+4,ROW($1:$9))),"")

yc1031

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


    ¯à§_½Ð°Ý¦bLOOKUP«e­±¨º­Ó "-" ¬O¤°»ò·N«ä©O?

hcm19522

https://blog.xuite.net/hcm19522/twblog/589160566

­ã´£³¡ªL

¦^´_ 4# yc1031


¨ú¥X¨Óªº¼Æ­È¬O"­t¼Æ" >> ¥[ "-" Âॿ¼Æ~~

Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ßVBA°}¦C,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:

¤U¸ü (55.8 KB)

2023-6-1 08:43

°õ¦æµ²ªG:

¤U¸ü (59.72 KB)

2023-6-1 08:43

Option Explicit
Sub TEST()
Dim Brr, Crr, A, i&, j%, M%
'¡ô«Å§iÅܼÆ
Brr = Range([A2], [A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HAÄæÀx¦s®æ­È±a¤J
ReDim Crr(1 To UBound(Brr), 1 To 100)
'¡ô¥OCrrÅܼƬO ¤GºûªÅ°}¦C,Áa¦V½d³ò¦PBrr°}¦C,¾î¦V1~100
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!³v¦C°õ¦æ
   If InStr(Brr(i, 1), "fee$") = 0 Then GoTo i01 Else: A = Split(Brr(i, 1), "fee$")
   '¡ô¦pªGBrr°}¦C­È¤£¥]§t "fee$"´N¸õ¨ì¼Ð¥Üi01¦ì¸mÄ~Äò°õ¦æ,
   '§_«h´N¥OAÅܼƬO ¤@ºû°}¦C,¥H"fee$" ¤À³ÎBrr°}¦C­È±a¤JA°}¦C¸Ì
   If M < UBound(A) Then M = UBound(A)
   '¡ô¦pªGMÅܼƤp©óA°}¦Cªº³Ì¤j¯Á¤Þ¸¹,´N¥OMÅܼƬO A°}¦Cªº³Ì¤j¯Á¤Þ¸¹
   For j = 1 To UBound(A): Crr(i, j) = Val(A(j)): Next
   '¡ô³]¶¶°j°é!±NA°}¦C­ÈÂà¤Æ¬°¼Æ­È«á¼g¤JCrr°}¦C¸Ì
i01: Next
[B2].Resize(UBound(Brr), M) = Crr
'¡ô¥OCrr°}¦C­È±q[B2]¶}©l¼g¤J¦J¦CªºÀx¦s®æ¤¤,¶W¹L½d³òªº°}¦C­È©¿²¤
Erase Brr, Crr, A
'¡ô¥OÄÀ©ñÅܼÆ
End Sub


'==================================================
'¸É¥R:¥H¤U¥t¤@ºØ±¡ªp
Sub TEST_1()
Dim Brr, Crr, A, i&, j%, M%
Brr = Range([A2], [A65536].End(3))
ReDim Crr(1 To UBound(Brr), 1 To 100)
For i = 1 To UBound(Brr)
   If InStr(Brr(i, 1), "fee") = 0 Then GoTo i01 Else: A = Split(Brr(i, 1), "fee")
   If M < UBound(A) Then M = UBound(A)
   For j = 1 To UBound(A): Crr(i, j) = Val(Replace(A(j), "$", "")): Next
i01: Next
[B2].Resize(UBound(Brr), M) = Crr
Erase Brr, Crr, A
End Sub