Split cell value where ", " to new cell

[hajiali]

Hello everyone,

Im needing help with the following
in column A I have String with commas

ex:

Book9
	A	B	C	D	E	F	G	H	I	J
1	1,OFF,03 TP 2-1,03 TP 2-1,03 TP 2-1,03 TP 2-1,03 TP 2-1,OFF,1
Sheet1

what I need done is a macro that will split the value where ", " to its once cell next to A so the result for the above should be the following:

Book9
	A	B	C	D	E	F	G	H	I	J
1	1,OFF,03 TP 2-1,03 TP 2-1,03 TP 2-1,03 TP 2-1,03 TP 2-1,OFF,1	1	OFF	03 TP 2-1	03 TP 2-1	03 TP 2-1	3 TP 2-1	3 TP 2-1	OFF	1
Sheet1

I will have multiple Rows so needs to run to the last row in column A. Any help is greatly appreciated.

Book9
	A
1	1,OFF,03 TP 2-1,03 TP 2-1,03 TP 2-1,03 TP 2-1,03 TP 2-1,OFF,1
2	2,OFF,04 TP 2-4,03C TP 2-2,03C TP 2-2,03C TP 2-2,03C TP 2-2,OFF,2
3	3,OFF,04 TP 2-2,03C TP 1-2,03C TP 1-2,03C TP 1-2,03C TP 1-2,OFF,2
4	4,OFF,OFF,03 TP 1-4,03 TP 1-4,03 TP 1-4,03 TP 1-4,03 TP 2-1,1
5	5,OFF,OFF,03B TP 2-4,03B TP 2-4,03B TP 2-4,03B TP 2-4,03B TP 2-4,1
6	6,OFF,OFF,05 TP/BR,05 TP/BR,05 TP/BR,05 TP/BR,05 TP/BR,1
7	7,03 TP 2-1,OFF,OFF,03 TP 1-1,03 TP 1-1,03 TP 1-1,03 TP 1-4,1
8	8,05 TP/BR,OFF,OFF,05 TP/BR,05 TP/BR,05 TP/BR,05 TP/BR,1
9	9,06 TP 2-3,OFF,OFF,05B TP 2-3,05B TP 2-3,05B TP 2-3,05B TP 2-3,2
10	10,03 TP 1-4,03 TP 1-4,OFF,OFF,04 TP 2-5,04 TP 2-5,03 TP 1-1,2
11	11,04 TP 2-4,04 TP 1-5,OFF,OFF,04 TP 1-5,04 TP 1-5,04 TP 2-2,1
12	12,04B TP/BR,04B TP 2-5,OFF,OFF,04B Misfits,04B Misfits,04B TP 2-5,1
13	13,03 TP 1-1,03 TP 1-1,03 TP 1-1,OFF,OFF,03 Misfits,03 Misfits,1
14	14,05 TP/BR,05 TP/BR,05 TP/BR,OFF,OFF,05 TP/BR,05 TP/BR,1
15	15,05B TP 2-5,05 TP/BR,05B TP 2-3,OFF,OFF,05 TP 1-3,05 TP 1-3,2
16	16,04 TP 2-2,04 TP 1-2,04 TP 2-5,04 TP 2-5,OFF,OFF,04 TP 1-5,1
17	17,04 TP 1-2,04 Misfits,04 TP 1-5,04 TP 1-5,OFF,OFF,04 TP 1-2,1
18	18,04B Misfits,04B TP 1-3,04B Misfits,04B Misfits,OFF,OFF,04B Misfits,1
19	19,03 Misfits,03 Misfits,03 Misfits,03 Misfits,03 Misfits,OFF,OFF,1
20	20,05 TP 1-5,05 TP 2-3,05 TP 1-3,05 TP 1-3,05 TP 1-3,OFF,OFF,1
21	21,06 TP 1-3,05 TP/BR,05 TP/BR,05 TP/BR,05 TP/BR,OFF,OFF,2
Sheet1

 

[Joe4]

You don't need VBA to do this.
Just use "Text to Columns" from the Data menu, selecting the comma as your delimiter, and it will do precisely that.

If you want/need it in VBA code, just turn on the Macro Recorder, and record yourself doing this with Text to Columns, and you will have the code that you need.

 

[Fluff]

How about

Sub hajiali()
   Dim Cl As Range
   Dim Sp As Variant
   
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      Sp = Split(Cl.Value, ",")
      Cl.Offset(, 1).Resize(, UBound(Sp) + 1).Value = Sp
   Next Cl
End Sub

 

[hajiali]

Much appreciate the quick response everyone. Thank you so much Fluff works great

 

[Fluff]

Glad we could help & thanks for the feedback.