I need help on how to do this... we have bom data on excel where one
cell as multiple values separated by comma(,). I need this values
transpose as rows (it's like the comma is symbol for a new row. Is
there a way to do this? here's an example of a data I originally have
on one cell "1,2,3,4,5,6,7" I need them to be on this format.

A(column)
1
2
3
4
5
6
7

Is this possible to do this on Excel? I appreciate any help....

Anything is possible on excel.
Try this. Change A1 to your cell.
And Cells(i + 1, 2), 2 = B, 1 = 1, so it is B1

Sub macro1()
Dim sArray() As String
sArray = Split(Range("A1").FormulaR1C1, ",")

Dim i As Long, last As Long
i = 0
last = UBound(sArray) - 1
Do Until i last
Cells(i + 1, 2).FormulaR1C1 = CStr(sArray(i))
i = i + 1
Loop
End Sub


that's it. Cheers.

"Marve" wrote:

I need help on how to do this... we have bom data on excel where one
cell as multiple values separated by comma(,). I need this values
transpose as rows (it's like the comma is symbol for a new row. Is
there a way to do this? here's an example of a data I originally have
on one cell "1,2,3,4,5,6,7" I need them to be on this format.

A(column)
1
2
3
4
5
6
7

Is this possible to do this on Excel? I appreciate any help....

Hi jChen,

It works! Thanks a lot! I have another question if you don't mind.
Using the same code. I'm thinking of transposing the new rows to a new
worksheet. Is there a way to do this?

I have another problem, how do I automatically run this peace if code
on selected cells? because it's tedious having to change the cell/s on
the code... is there a way to do this based on range of cells selected?
Thanks
Appreciate any help...

jchen wrote:
Anything is possible on excel.
Try this. Change A1 to your cell.
And Cells(i + 1, 2), 2 = B, 1 = 1, so it is B1

Sub macro1()
Dim sArray() As String
sArray = Split(Range("A1").FormulaR1C1, ",")

Dim i As Long, last As Long
i = 0
last = UBound(sArray) - 1
Do Until i last
Cells(i + 1, 2).FormulaR1C1 = CStr(sArray(i))
i = i + 1
Loop
End Sub


that's it. Cheers.

"Marve" wrote:

I need help on how to do this... we have bom data on excel where one
cell as multiple values separated by comma(,). I need this values
transpose as rows (it's like the comma is symbol for a new row. Is
there a way to do this? here's an example of a data I originally have
on one cell "1,2,3,4,5,6,7" I need them to be on this format.

A(column)
1
2
3
4
5
6
7

Is this possible to do this on Excel? I appreciate any help....

I already figured this out.


Marve wrote:
I have another problem, how do I automatically run this peace if code
on selected cells? because it's tedious having to change the cell/s on
the code... is there a way to do this based on range of cells selected?
Thanks
Appreciate any help...

jchen wrote:
Anything is possible on excel.
Try this. Change A1 to your cell.
And Cells(i + 1, 2), 2 = B, 1 = 1, so it is B1

Sub macro1()
Dim sArray() As String
sArray = Split(Range("A1").FormulaR1C1, ",")

Dim i As Long, last As Long
i = 0
last = UBound(sArray) - 1
Do Until i last
Cells(i + 1, 2).FormulaR1C1 = CStr(sArray(i))
i = i + 1
Loop
End Sub


that's it. Cheers.

"Marve" wrote:

I need help on how to do this... we have bom data on excel where one
cell as multiple values separated by comma(,). I need this values
transpose as rows (it's like the comma is symbol for a new row. Is
there a way to do this? here's an example of a data I originally have
on one cell "1,2,3,4,5,6,7" I need them to be on this format.

A(column)
1
2
3
4
5
6
7

Is this possible to do this on Excel? I appreciate any help....