[發問] 只想取部份字元

john2006168

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

下載次數: 18

2011-11-2 00:39

Hsieh

回復 1# john2006168


    如果是以斜線座分隔符號
資料剖析即可達到

john2006168

回復 2# Hsieh


可以 用"/"     請問用vba 怎麼寫.

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

複製代碼

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 發表於 2011-11-2 16:09

    如果有些字沒有”/”,保持那些字不變,如下

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

下載次數: 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

複製代碼

john2006168

回復 8# GBKEE


    Thanks,這是我想要的answer

john2006168

回復 7# Hsieh


    Thanks