[µo°Ý] ¥u·Q¨ú³¡¥÷¦r¤¸

john2006168

[attach]8398[/attach] TEST03.zip (11.51 KB)

¤U¸ü¦¸¼Æ: 18

2011-11-2 00:39

Hsieh

¦^´_ 1# john2006168


    ¦pªG¬O¥H±×½u®y¤À¹j²Å¸¹
¸ê®Æ­åªR§Y¥i¹F¨ì

john2006168

¦^´_ 2# Hsieh


¥i¥H ¥Î"/"     ½Ð°Ý¥Îvba «ç»ò¼g.

GBKEE

¦^´_ 3# john2006168

1. Sub Ex()
   
2.     Dim i As Integer, S As Integer
   
3.     i = 2
   
4.     With Sheet2
   
5.         Do While .Cells(i, "A") <> ""
   
6.             ''''''Split ¨ç¼Æ ''''''''''''''''''''''''''''''''''''''''
   
7.              '.Cells(i, "C") = Split(.Cells(i, "A"), "/")(1)
   
8.             ''''''InStr + MID   ¨ç¼Æ'''''''''''''''''''''''''''''''''
   
9.             S = InStr(.Cells(i, "A"), "/")
   
10.             .Cells(i, "C") = Mid(.Cells(i, "A"), S + 1)
    
11.             
    
12.             i = i + 1
    
13.         Loop
    
14.     End With
    
15. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 3# john2006168


    Range("A2:A4").TextToColumns _
DataType:=xlDelimited, Destination:=[C2], _
Other:=True, OtherChar:="/", FieldInfo:=Array(Array(1, 9), Array(2, 1))

john2006168

¦^´_  john2006168
GBKEE µoªí©ó 2011-11-2 16:09

    ¦pªG¦³¨Ç¦r¨S¦³¡¨/¡¨,«O«ù¨º¨Ç¦r¤£ÅÜ,¦p¤U

A                            B            C
JOHN/JOHN1234                JOHN1234
JOHN/JOHN1234                JOHN1234
JOHN               
PETER                test003.zip (11.1 KB)

¤U¸ü¦¸¼Æ: 8

2011-11-2 18:05

Hsieh

¦^´_ 6# john2006168


For Each a In Range([A3], [A3].End(xlDown))
a.Offset(, 2) = Split(a, "/")(UBound(Split(a, "/")))
Next

GBKEE

¦^´_ 6# john2006168

1. Sub Ex()
   
2.     Dim i As Integer, S As Integer
   
3.     i = 2
   
4.     With Sheet2
   
5.         Do While .Cells(i, "A") <> ""
   
6.             S = InStr(.Cells(i, "A"), "/")
   
7.             If S > 0 Then
   
8.              '.Cells(i, "C") = Split(.Cells(i, "A"), "/")(1)
   
9.             .Cells(i, "C") = Mid(.Cells(i, "A"), S + 1)
   
10.             .Cells(i, "A") = Left(.Cells(i, "A"), S - 1)
    
11.             End If
    
12.             i = i + 1
    
13.         Loop
    
14.     End With
    
15. End Sub

½Æ»s¥N½X

john2006168

¦^´_ 8# GBKEE


    Thanks,³o¬O§Ú·Q­nªºanswer

john2006168

¦^´_ 7# Hsieh


    Thanks