Formula or VBA to split cell data into 5 columns

[mcgee1505]

Hi All,

I have searched the forum for a solution but nothing seems to fit what I need.

I have a worksheet with thousands of rows of data covering columns A - P. In column A is a product code set out in 5 sections with a 'dash' seperator e.g. 123456-ABC-ABC-ABC-123456. I would like a formula or VBA code to take the product code in column A and split it across columns Q - U. Can anyone help please?

Thanks in advance.

 

[AlanY]

you can use excel's built-in function text-to-column with "-" as delimiter

 

[mohadin]

May be

Sub test()
    Dim i
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        Range("Q" & i).Resize(, 5) = Split(Cells(i, 1), "-")
    Next
End Sub

 

[mcgee1505]

Thanks AlanY but I tried that and the last section number didn't come across the same e.g if it is 000001 it transferes as 1, I need the number to be the same.

Thanks mohadin, this works good but it is very slow at running the code. I takes approx 2 seconds a row and there are thousands of rows, is there a way to make it run faster?

 

[mohadin]

Try

Sub test()
    Dim i, ii, a
    a = Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1)
    ReDim b(1 To UBound(a), 1 To 5)
    For i = 1 To UBound(a)
        For ii = 1 To 5
            b(i, ii) = Split(a(i, 1), "-")(ii - 1)
        Next
    Next
    Range("Q2").Resize(UBound(b), UBound(b, 2)) = b
End Sub

 

[mcgee1505]

Thanks mohadin, this was very fast but same as the excel function above, leaves the last number as a single digit but it needs to remain a six digit number, I need the number to be the same?

 

[mohadin]

But they are the same
maybe the cells size?

 

[mohadin]

 

[Fluff]

You can use text to columns, but on page 3 of the wizard in the data preview section select the last column & then click text under the data format.

 

[mcgee1505]

I have even formatted the columns to 'General' but it still doesn't work?

Number
123456-ABC-ACR-EMF-000001	Data deleted for confidentiality															123456	ABC	ACR	EMF	1
123456-ABC-ACR-EMF-000002																123456	ABC	ACR	EMF	2
123456-ABC-ACR-EMF-000003																123456	ABC	ACR	EMF	3
123456-ABC-ACR-EMF-000004																123456	ABC	ACR	EMF	4
123456-ABC-ACR-EMF-000005																123456	ABC	ACR	EMF	5
123777-ABC-ACR-EMF-000154																123777	ABC	ACR	EMF	154
123777-ABC-ACR-EMF-000155																123777	ABC	ACR	EMF	155
123777-ABC-ACR-EMF-000156																123777	ABC	ACR	EMF	156
123777-ABC-ACR-EMF-000157																123777	ABC	ACR	EMF	157
123777-ABC-ACR-EMF-000158																123777	ABC	ACR	EMF	158
123777-ABC-ACR-EMF-000159																123777	ABC	ACR	EMF	159
123777-ABC-ACR-EMF-000160																123777	ABC	ACR	EMF	160
435666-ABC-GGG-EMF-000352																435666	ABC	GGG	EMF	352
435666-ABC-GGG-EMF-000353																435666	ABC	GGG	EMF	353
435666-ABC-GGG-EMF-000354																435666	ABC	GGG	EMF	354
435666-ABC-GGG-EMF-000355																435666	ABC	GGG	EMF	355
435666-ABC-GGG-EMF-000356																435666	ABC	GGG	EMF	356
435666-ABC-GGG-EMF-000357																435666	ABC	GGG	EMF	357
123489-ABC-TBT-SSS-000002																123489	ABC	TBT	SSS	2
123489-ABC-TBT-SSS-000003																123489	ABC	TBT	SSS	3
123489-ABC-TBT-SSS-000004																123489	ABC	TBT	SSS	4
123489-ABC-TBT-SSS-000005																123489	ABC	TBT	SSS	5
123489-ABC-TBT-SSS-000006																123489	ABC	TBT	SSS	6
123489-ABC-TBT-SSS-000007																123489	ABC	TBT	SSS	7
123489-ABC-TBT-SSS-000008																123489	ABC	TBT	SSS	8