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

[µo°Ý] ¤ÀÂ÷³æ¸¹

[µo°Ý] ¤ÀÂ÷³æ¸¹

¤j¤j¦n,
EÄ檺³æ¸¹,§t¦³2­Ó"-",§@¬°¤ÀÂ÷°ò·Ç,³æ¸¹¤ÀÂ÷¦ÜR¡BS¡BT¤TÄ椤
TÄæ³æ¸¹: 3½X+"-" (¤£¨¬3½X,«e­±¸É0)
SÄæ³æ¸¹: 4½X+"-" (¤£¨¬4½X,«e­±¸É0)
RÄæ³æ¸¹: 3½X (¤£¨¬3½X,«e­±¸É0)

ex:
±ÄÁʳ渹½X        ¥½½X        ¤¤¶¡½X        ³æ¸¹
886-1215-283        283        1215-        886-
872-1305-017        017        1305-        872-
010-0105-547        547        0105-        010-

½Ð°Ý­n¦p¦ó¼g³o­Óµ{¦¡?   ¤ÀÂ÷³æ¸¹_Split.rar (151.35 KB)
  1. Sub ¤ÀÂ÷³æ¸¹()
  2. Dim xB As Workbook, R&, xD, Arr2, Arr3, Arr4
  3. R = Cells(Rows.Count, "E").End(xlUp).Row
  4. If R <= 2 Then Exit Sub
  5. Set xB = Workbooks("¤ÀÂ÷³æ¸¹_Split.xlsx")
  6. With xB.Sheets("¥_°Ï")
  7.      R = .Cells(Rows.Count, 1).End(xlUp).Row - 1
  8.     If R > 0 Then arr = Split([E2], "-")
  9.      If R > 0 Then Arr2 = Split([E2], "-").Resize(R).Value
  10.      If R > 0 Then Arr3 = Split([E2], "-").Resize(R).Value
  11.      If R > 0 Then Arr4 = Split([E2], "-").Resize(R).Value
  12. End With
  13.     xB.Sheets("¥_°Ï").[r2].Resize(R).Value = Arr2
  14.     xB.Sheets("¥_°Ï").[s2].Resize(R).Value = Arr3
  15.     xB.Sheets("¥_°Ï").[t2].Resize(R).Value = Arr4
  16. Next
  17. End Sub
½Æ»s¥N½X

Sub aaa()
r = Cells(Rows.Count, "E").End(3).Row

Dim ar
ReDim ar(3 To r, 1 To 3)
For i = 3 To r
x = Cells(i, "e")
If x <> "" Then
If Len(x) - Len(Replace(x, "-", "")) = 2 Then

x0 = Split(x, "-")
ar(i, 1) = "'" & x0(2)
ar(i, 2) = x0(1) & "-"
ar(i, 3) = x0(0) & "-"

End If
End If
Next

Range("r3").Resize(r - 2, 3) = ar
End Sub

TOP

¦^´_ 1# PJChen

¬Ý§A­ì¥»ªº¸ê®Æ¦n¹³¨S¦³»Ý­n¸É"0" ªº¦a¤è?

Sub ¤ÀÂ÷³æ¸¹()
Dim Arr, R&
Arr = Range([E2], [E2].End(4))
If Arr(UBound(Arr), 1) = "" Then Exit Sub   '¨S¸ê®Æ
Columns("R:T").NumberFormatLocal = "@"
ReDim Brr(1 To UBound(Arr), 1 To 3)
For R = 2 To UBound(Arr)
  If Arr(R, 1) Like "*-*-*" Then
    Brr(R, 1) = Split(Arr(R, 1), "-")(2)
    Brr(R, 2) = Split(Arr(R, 1), "-")(1) & "-"
    Brr(R, 3) = Split(Arr(R, 1), "-")(0) & "-"
    If Len(Brr(R, 1)) < 3 Then Brr(R, 1) = String(3 - Len(Brr(R, 1)), "0") & Brr(R, 1)
    If Len(Brr(R, 2)) < 5 Then Brr(R, 2) = String(5 - Len(Brr(R, 2)), "0") & Brr(R, 2)
    If Len(Brr(R, 3)) < 4 Then Brr(R, 3) = String(4 - Len(Brr(R, 3)), "0") & Brr(R, 3)
  End If
Next R
[R2].Resize(UBound(Brr), 3) = Brr
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 1# PJChen

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub test()
Dim Arr, Brr(), a, w$, i&, j%
Columns("R:T").NumberFormatLocal = "@"
Arr = Range([E2], [E2].End(4))
ReDim Brr(1 To UBound(Arr), 1 To 3)
For i = 2 To UBound(Arr)
    a = Split(Arr(i, 1), "-")
    If UBound(a) < 2 Then GoTo 99
    For j = 0 To UBound(a)
        w = Split(Arr(i, 1), "-")(j)
        If j = 0 Then
            If Len(w) < 3 Then Brr(i, 3) = "0" & w & "-" Else Brr(i, 3) = w & "-"
        ElseIf j = 1 Then
            If Len(w) < 4 Then Brr(i, 2) = "0" & w & "-" Else Brr(i, 2) = w & "-"
        ElseIf j = 2 Then
            If Len(w) < 3 Then Brr(i, 1) = "0" & w Else Brr(i, 1) = w
        End If
    Next
99: Next
[R2].Resize(UBound(Brr), 3) = Brr
End Sub

TOP

¦^´_ 3# n7822123
¦^´_ 2# singo1232001
¦^´_ 4# samwang

ÁÂÁ¤T¦ì, µ²ªG³£«Ü§¹¬ü¡I
½Ð°ÝN¤j (n7822123)
¯à§_À°¦£¸Ñ»¡¥H¤Uµ{¦¡¬õ¦r? §Ú¹ïUBoundªº¼Æ¦r¹ïÀ³«Ü¤£²z¸Ñ@@
.NumberFormatLocal = "@"
ReDim Brr(1 To UBound(Arr), 1 To 3)
For R = 2 To UBound(Arr)

TOP

¦^´_ 5# PJChen


ubound(arr) ... 緃¦V¦æ¼Æ...¬Û·í¤_«ü©w½d³òªºrows.count
ubound(arr,2) ... ¾î¦VÄæ¼Æ...¬Û·í¤_«ü©w½d³òªºcolumns.count

for i = 2 to ~~
arrªº½d³ò¥]§t²Ä¤@¦æ¼ÐÃD, ²Ä¤@¦æ¤£¶·³B²z, ¬G±q2°_©l~~

TOP

­Y³£¬O¯Â¼Æ¦r~~
Sub TEST_A1()
Dim Arr, i&, j%, Vr, TR
Arr = Range([g1], [e65536].End(3))
Vr = Array("000-", "0000-", "000")
For i = 3 To UBound(Arr)
    TR = Split(Arr(i, 1) & "--", "-")
    For j = 0 To 2
        Arr(i - 2, 3 - j) = IIf(IsNumeric(TR(j)), Format(TR(j), Vr(j)), "")
    Next j
Next i
With [r3].Resize(UBound(Arr) - 2, 3)
     .NumberFormatLocal = "@"
     .Value = Arr
End With
End Sub

'================================

TOP

¦^´_ 7# ­ã´£³¡ªL
¦n¤[¤£¨£....­ã¤j¦n,
µ{¦¡¹B§@OK

¯à§_¸Ñ¬õ¦r?
Arr = Range([g1], [e65536].End(3))
¥tµ{¦¡¤¤¨S¦³R:T, ¬°¦ó¥¦¯à¦Û°Ê¸m¤J©O?

TOP

¦^´_ 5# PJChen

©w¸qBrr¬O­Ó2ºû°}¦C¡A¥¦ªº1ºû¤W¬Éµ¥©óArr°}¦Cªº1¬°¤W¬É(Rows)¡A2ºû¬É­­¬O1~3(Columns)

¥H¤U³o­Ó½d¨Òµ{¦¡¥i¥HÀ°§A¤F¸Ñ °}¦Cªº«Å§i¡A¥H¤Î°}¦Cªº¬É­­°Ï¶¡

ª½±µ°õ¦æ´N¥i¥H¤F¡A¬Ý°T®§¨Ã¹ï¤ñµ{¦¡


Sub Ex()
Dim Arr(1 To 10)                  '©w¸q¤@­Ó¤@ºû°}¦CArr
Str1 = "Arrªº¬É­­=" & LBound(Arr) & "~" & UBound(Arr) & vbCrLf & vbCrLf
Dim Brr(2 To 20, 3 To 30)   '©w¸q¤@­Ó¤Gºû°}¦CBrr
Str2 = "Brrªº1ºû¬É­­=" & LBound(Brr, 1) & "~" & UBound(Brr, 1) & vbCrLf
Str3 = "Brrªº2ºû¬É­­=" & LBound(Brr, 2) & "~" & UBound(Brr, 2) & vbCrLf
MsgBox Str1 & Str2 & Str3
End Sub
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2021-7-6 00:56 ½s¿è

¦^´_ 9# n7822123

°õ¦æ«á,ı±o«Ü©â¶H@@

TOP

        ÀR«ä¦Û¦b : ¡i»X½ªªº¦Û¥Ñ¡j¤H±`¦b¤°»ò³£¥i¥H¦Û¥Ñ¦Û¦bªº®É­Ô¡A«o³Q³oºØÀH¤ß©Ò±ýªº¦Û¥Ñ»X½ª¡AµêÂY®É¥ú¦Ó²@µLıª¾¡C
ªð¦^¦Cªí ¤W¤@¥DÃD