[µ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®¦ ¦U¦ì¤j¤jªºµL¨p¤À¨É\

yen956

¦^´_ 9# ­ã´£³¡ªL
ªGµM¬O¦n¤èªk:
¤]¾Ç¨ì¤F Mid¤ÎOffset¨ç¼Æ ªº ²¤S·s ¤èªk, ÁÂÁÂ!!

c_c_lai

¦^´_ 10# ­ã´£³¡ªL
A5 = "003ª÷¤j¤¤"
°õ¦æ ¤À³Î() µ²ªG¬°¡G    3
B5 := =MID(LOOKUP(,-LEFT(1&A5,ROW($1:$15))),3,15)
«h B5  =  "003"

Cells(I, 2) = "'" & Left(Str1, J - 1) ªº¤è¦¡
«h B5  =  "'003"

ML089

9 ¼Óªº
xR(1, 2) = Mid(U, 2)
§ï¬°
xR(1, 2) = "'" & Mid(U, 2)
±j­¢¼g¤J¬°¤å¦r

­ã´£³¡ªL

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

­ã´£³¡ª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Åã¥Ü

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

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

­ã´£³¡ªL

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

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