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

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

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

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

¨ú¥X©T©w¦r¦ê«áªº¼Æ¦r.zip (6.35 KB)

joyce

  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

TOP

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

TOP

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


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

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 4# yc1031


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

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-6-1 08:53 ½s¿è

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

°õ¦æ«e:
20230601_1.jpg
2023-6-1 08:43


°õ¦æµ²ªG:
20230601_2.jpg
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
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD