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.

 

[Fluff]

In the wizard did you select the final column & specify text?
When I do that I get

+Fluff v2.xlsm
	A																Q	R	S	T	U
1	Number
2	123456-ABC-ACR-EMF-000001																123456	ABC	ACR	EMF	000001
3	123456-ABC-ACR-EMF-000002																123456	ABC	ACR	EMF	000002
4	123456-ABC-ACR-EMF-000003																123456	ABC	ACR	EMF	000003
5	123456-ABC-ACR-EMF-000004																123456	ABC	ACR	EMF	000004
6	123456-ABC-ACR-EMF-000005																123456	ABC	ACR	EMF	000005
7	123777-ABC-ACR-EMF-000154																123777	ABC	ACR	EMF	000154
8	123777-ABC-ACR-EMF-000155																123777	ABC	ACR	EMF	000155
9	123777-ABC-ACR-EMF-000156																123777	ABC	ACR	EMF	000156
10	123777-ABC-ACR-EMF-000157																123777	ABC	ACR	EMF	000157
11	123777-ABC-ACR-EMF-000158																123777	ABC	ACR	EMF	000158
12	123777-ABC-ACR-EMF-000159																123777	ABC	ACR	EMF	000159
13	123777-ABC-ACR-EMF-000160																123777	ABC	ACR	EMF	000160
14	435666-ABC-GGG-EMF-000352																435666	ABC	GGG	EMF	000352
15	435666-ABC-GGG-EMF-000353																435666	ABC	GGG	EMF	000353
16	435666-ABC-GGG-EMF-000354																435666	ABC	GGG	EMF	000354
17	435666-ABC-GGG-EMF-000355																435666	ABC	GGG	EMF	000355
18	435666-ABC-GGG-EMF-000356																435666	ABC	GGG	EMF	000356
19	435666-ABC-GGG-EMF-000357																435666	ABC	GGG	EMF	000357
20	123489-ABC-TBT-SSS-000002																123489	ABC	TBT	SSS	000002
21	123489-ABC-TBT-SSS-000003																123489	ABC	TBT	SSS	000003
22	123489-ABC-TBT-SSS-000004																123489	ABC	TBT	SSS	000004
23	123489-ABC-TBT-SSS-000005																123489	ABC	TBT	SSS	000005
24	123489-ABC-TBT-SSS-000006																123489	ABC	TBT	SSS	000006
25	123489-ABC-TBT-SSS-000007																123489	ABC	TBT	SSS	000007
26	123489-ABC-TBT-SSS-000008																123489	ABC	TBT	SSS	000008
Data

 

[mcgee1505]

OK Fluff, I have done it as Text-to-Columns and specified the last column as General. This works so I've recoreded it as a Macro and added it as a butto on my custom menu.

Sub Tests()
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        2)), TrailingMinusNumbers:=True
End Sub

 

[Fluff]

Glad we could help & thanks for the feedback.