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

[µo°Ý] ¦a§}¸ê®Æ¤À³Î¦¨¦hÄæ

[µo°Ý] ¦a§}¸ê®Æ¤À³Î¦¨¦hÄæ

½Ð°Ý¦a§}¸ê®Æ­n¤À³Î¦¨¦hÄæ B2¡BC2¤½¦¡¡HÁÂÁ¡I

Image 2.jpg
2021-11-18 08:17


¦a§}.zip (9.04 KB)

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

TOP

¦^´_ 1# aer

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test()
Dim Arr, a, i&
Arr = Range([d1], [a65536].End(3))
For i = 2 To UBound(Arr)
    Arr(i, 2) = Left(Arr(i, 1), 3)
    a = Mid(Arr(i, 1), 4)
    If InStr(a, "¶m") Then
        Arr(i, 3) = Split(a, "¶m")(0) & "¶m"
        Arr(i, 4) = Split(a, "¶m")(1)
    ElseIf InStr(a, "Âí") Then
        Arr(i, 3) = Split(a, "Âí")(0) & "Âí"
        Arr(i, 4) = Split(a, "Âí")(1)
    ElseIf InStr(a, "¥«") Then
        Arr(i, 3) = Split(a, "¥«")(0) & "¥«"
        Arr(i, 4) = Split(a, "¥«")(1)
    ElseIf InStr(a, "°Ï") Then
        Arr(i, 3) = Split(a, "°Ï")(0) & "°Ï"
        Arr(i, 4) = Split(a, "°Ï")(1)
    End If
Next
Range("a1").Resize(UBound(Arr), 4) = Arr
End Sub

TOP

¦^´_ 2# hcm19522

¤½¦¡´ú¸Õ¨S°ÝÃD¡A·PÁ±z¡I

TOP

¦^´_ 3# samwang

´ú¸Õ¨S°ÝÃD¡A·PÁ±z¡I

TOP

B2:
=LEFT(A2,3)

C2:
=MID(LEFT(A2,-LOOKUP(,-FIND({"¶m","Âí","¥«","°Ï"},A2,4))),4,9)

D2:
=MID(A2,LEN(B2&C2)+1,99)

­Y¦a§}³W«h¤£½T©w, ­n¥t·Q¿ìªk~~

TOP

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

´ú¸Õ¨S°ÝÃD¡A·PÁ±z¡I

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2021-11-19 13:12 ½s¿è

¦a§}¤½¦¡«ÜÃø100%§¹¥þ¬O¹ïªº
«Øij¤½¦¡³B²z§¹ÁÙ¬O­n³vµ§¬Ý¬Ý¡A¤~¤£·|»~¨Æ¡C
¤×¨äÂ÷®q¡B¤s°Ïªº¦a§}­n®æ¥~¤p¤ß¡C

¤Wºô¬d¬Ý110¦~¦æ¬F°Ï¦W»P¸ô¦W
¿¤¥« 3­Ó¦r
¶mÂí¥«°Ï ³Ì¦h4­Ó¦r
¸ô¦W ³Ì¦h9­Ó¦r
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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

°õ¦æµ²ªG:
20231120_1.jpg
2023-11-20 13:04



Option Explicit
Sub TEST()
Dim Brr, Pq, Pa, Q, i&, j%, N%, c%, Y&, T$
Range([B2], Cells(Rows.Count, 1).End(xlUp)(1, 4)).ClearContents
Brr = Range([D1], Cells(Rows.Count, 1).End(xlUp))
Q = [{"¶m","Âí","¥«","°Ï"}]
For i = 2 To UBound(Brr)
   T = Brr(i, 1): Brr(i - 1, 1) = Left(T, 3): T = Mid(T, 4)
   For j = 1 To 4
      Pq = Split(T, Q(j))
      If UBound(Pq) > 0 Then c = j: N = N + 1: Pa = Pq
   Next
   If N = 1 Then
      Brr(i - 1, 2) = Pa(0) & Q(c)
      Brr(i - 1, 3) = Mid(T, Len(Pa(0)) + 2)
      Else: Y = Y + 1
   End If
   N = 0
Next
[B2].Resize(UBound(Brr) - 1, 3) = Brr
If Y > 0 Then MsgBox "¦³ " & Y & " ­ÓµLªk¿ë§O!"
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤H­nª¾ºÖ¡B±¤ºÖ¡B¦A³yºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD