hi ,
i have column A as
saa-n15-7hy
sma-n1n-8hy
saa-n15-9hy
i wanted all the middle part in cloumn B (excel formaula =MID(A2,5,3)) and last three parts in cloumn C (=MID(A2,9,3)).
below code is not working for me
hi ,
i have column A as
saa-n15-7hy
sma-n1n-8hy
saa-n15-9hy
i wanted all the middle part in cloumn B (excel formaula =MID(A2,5,3)) and last three parts in cloumn C (=MID(A2,9,3)).
below code is not working for me
Re: code rectification needed
Hello Libin,
Why dont you give this code a go:
Sub Right1()
Dim sSplit() As String
Dim data As Range
Dim c As Range
Set data = ActiveSheet.Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each c In data
sSplit = Split(c.Value, "-")
' c = sSplit(0) 'uncomment this line if you want the starting 3 letters only in column A
c.Offset(0, 1) = sSplit(1)
c.Offset(0, 2) = sSplit(2)
Next c
End Sub
Display More
Let me know if it works for you
Many Thanks
Caleeco
Re: code rectification needed
Hi...
The following code might be of use if you have a large data set as it has minimal sheet interaction..
Sub Splitter()
Dim a, b, i As Long
a = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
ReDim b(UBound(a) - 1, 2 To 3)
For i = 0 To UBound(a) - 1
If a(i + 1, 1) <> "" Then
b(i, 2) = Split(a(i + 1, 1), "-")(1)
b(i, 3) = Split(a(i + 1, 1), "-")(2)
End If
Next i
Range("B1").Resize(UBound(a), 2).Value = b
End Sub
Display More
Having said that. you didn't mention what you wanted in Column A for your output..
If you just want to separate it all by the hyphen.. maybe this also..
Don’t have an account yet? Register yourself now and be a part of our community!