[µo°Ý] ¨D§U split°ÝÃD ·P®¦

¾{³½µ·

¤U¸ü (17.07 KB)

2015-12-5 11:30

§Æ±æ±NAÄæ ¤À³Î¦¨BÄæ¼Æ¦r  CÄæ¤å¦r  ·P®¦

Sub ¤À³Î()
    Dim i As Integer
    With Sheet2
        .Cells(1, 2) = "½s¸¹"
        .Cells(1, 3) = "¦WºÙ"
         For i = 2 To .Cells(65536, 6).End(xlUp).Row
             Sheet2.Cells(i, 2) = Split(.Cells(i, 1), "  ")(0)
             Sheet2.Cells(i, 3) = Split(.Cells(i, 1), "  ")(1)
    Next i
End With
End Sub

¾{³½µ·

¤p§Ìªºµ{¦¡½X ³£µLªk°õ¦æ  ½ÐÀ°¦£«üÂI­×§ï  ·P®¦

hcm19522

http://blog.xuite.net/hcm19522/twblog/359339379
Ãþ¦üÃD

yen956

¦]­ì¦r¦ê¨S¦³ªÅ¥Õ, ¬G¤£¯à¥ÎªÅ¥Õ¥h Split
¸Õ¸Õ¬Ý

1. Sub splitTXT()
   
2.    Dim Str1 As String
   
3.    Dim I As Integer, J As Integer, ST As Integer
   
4.    For I = 2 To [A65536].End(xlUp).Row
   
5.        J = 1
   
6.        Num = ""
   
7.        Str1 = Cells(I, 1)
   
8.        For J = 1 To Len(Str1)
   
9.            ST = InStr(1, "0123456789", Mid(Str1, J, 1), 1)
   
10.            If ST = 0 Then
    
11.                Cells(I, 2) = Left(Str1, J - 1)
    
12.                Cells(I, 3) = Right(Str1, Len(Str1) - J + 1)
    
13.                Exit For
    
14.            End If
    
15.        Next
    
16.    Next

½Æ»s¥N½X

¾{³½µ·

·PÁ¼ӤW¨â¦ì¤j¤jªºÀ°¦£

¥t¥~ ½Ð±Ðyen956

1.  num=""  ¥Nªíªº¬O?

2.¥t¥~°²³]  A1Àx¦s®æ¬O 0911¤ý¤p©ú   ­n¦p¦ó¤À¹j¦¨B1=0911  C1=¤ý¤p©ú

¦]¬°0911ªº¹s·|³Q¬Ù²¤±¼  Åܦ¨911

·P®¦

­ã´£³¡ªL

¦^´_ 5# ¾{³½µ·

³Ì¦n´£¨Ñ§ó¦hªº¨Ò¤l¡A
½s¸¹³Ì¦h´X¦ì¼Æ¡H¦³µL§t­^¤å©Î²Å¸¹¡H
¦WºÙ«á­±¦³µL¥]§t¼Æ¦r¡H

Joforn

1. Sub SplitText()
   
2.   Dim I       As Long, J As Long
   
3.   Dim RegExp  As Object
   
4.   Dim Match   As Object
   
5.   Dim strNum  As String
   
6.   Dim strValue  As String
   
7.   
   
8.   Set RegExp = CreateObject("VBScript.RegExp")
   
9.   With Sheet2
   
10.     With RegExp
    
11.       .Global = True
    
12.       .Pattern = "\d+"
    
13.     End With
    
14.     For I = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
    
15.       strValue = .Cells(I, 1).Value
    
16.       Set Match = RegExp.Execute(strValue)
    
17.       If Match.Count Then
    
18.         strNum = Match.Item(0)
    
19.         .Cells(I, 2).Value = strNum
    
20.         J = InStr(strValue, strNum) + Len(strNum)
    
21.         If J Then .Cells(I, 3).Value = Mid$(strValue, J)
    
22.         Set Match = Nothing
    
23.       Else
    
24.         .Cells(I, 3).Value = strValue
    
25.       End If
    
26.     Next I
    
27.   End With
    
28. End Sub

½Æ»s¥N½X

yen956

¦^´_ 5# ¾{³½µ·
°w¹ï 0911 ­×¥¿, ¸Õ¸Õ¬Ý

1. '
   
2. Sub splitTXT()
   
3.    Dim Str1 As String
   
4.    Dim I As Integer, J As Integer, ST As Integer
   
5.    For I = 2 To [A65536].End(xlUp).Row
   
6.        J = 1
   
7. '       Num = ""    ³o¬O´ú¸Õ®É¥[¤W¥hªº, §Ñ¤F¥h±¼, Sorry!!
   
8.        Str1 = Cells(I, 1)
   
9.        For J = 1 To Len(Str1)
   
10.            ST = InStr(1, "0123456789", Mid(Str1, J, 1), 1)
    
11.            If ST = 0 Then
    
12.                Cells(I, 2) = "'" & Left(Str1, J - 1)     '°w¹ï 0911 ­×¥¿
    
13.                Cells(I, 3) = Right(Str1, Len(Str1) - J + 1)
    
14.                Exit For
    
15.            End If
    
16.        Next
    
17.    Next
    
18. End Sub

½Æ»s¥N½X

­ã´£³¡ªL

Sub ¤À³Î()
Dim xR As Range, U
For Each xR In Range([A2], [A65536].End(xlUp))
¡@¡@U = Val(1 & xR)
¡@¡@xR(1, 2) = Mid(U, 2)
¡@¡@xR(1, 3) = Mid(xR, Len(U))
Next
End Sub

¢°¡D½s¸¹­­¡e¯Â¼Æ¦r¡f¡A³Ì¦h¢°¢³½X
¢±¡D¢ÐÄæ¹w¥ý³]¬°¡e¤å¦r®æ¦¡¡f¡A¥H§K¡e«e¾É¢¯¡fµLªkÅã¥Ü

­ã´£³¡ªL

¤½¦¡¡G¢Ð¡þ¢ÑÄæ³]³q¥Î®æ¦¡
¢Ð¢±¡G=MID(LOOKUP(,-LEFT(1&A2,ROW($1:$15))),3,15)
¢Ñ¢±¡G=MID(A2,LEN(B2)+1,99)