contents of cells to rows
 

hi,

is it possible to convert the contents of cells to rows eg. i have rows of
cells which contains numbers seperated by a comma (542212, 542213, 542214
etc).

Is there a way of doing this without using the text to column first?

contents of cells to rows
 

Text to columns is the best way, but the following UDF will do the same thing:

Public Function items(r As Range, whichone As Integer)
ary = Split(r.Value, ",")
items = ary(whichone - 1)
End Function

So with your data in A1, enter:

=items(A1,1) =items(A1,2) =items(A1,3) =items(A1,4)

in some row
--
Gary''s Student - gsnu200804


"Hardworker" wrote:

hi,

is it possible to convert the contents of cells to rows eg. i have rows of
cells which contains numbers seperated by a comma (542212, 542213, 542214
etc).

Is there a way of doing this without using the text to column first?

contents of cells to rows
 

Hi Hardworker,

Say you make a macro enabled worksheet. In A2 put "5,6,7,8" in Column C2,
go into developer and pick insert, add a button.

In the button code, put the following:

Sub Button1_Click()
Dim texter As String

Dim datatocolumn() As String

texter = Sheet1.Range("A2")
datatocolumn = Split(texter, ",")
Dim i As Integer

i = 5

Dim item As Variant
For Each item In datatocolumn

Sheet1.Range("A" & Trim(Str(i))).Value = item
i = i + 1

Next item


End Sub


That would do it, but I'm really not sure why you wouldn't want to use text
to column.

In any event, I hope that this is the answer you are looking for.

John



"Hardworker" wrote:

hi,

is it possible to convert the contents of cells to rows eg. i have rows of
cells which contains numbers seperated by a comma (542212, 542213, 542214
etc).

Is there a way of doing this without using the text to column first?

contents of cells to rows
 

Hi Gary,

thanks for the help. i am not too familiar with coding in excel. Do i just
paste this in the VB Editor?

I think i did what you said i should but it gives me a messgae of "#NAME?"

What am i doing wrong?

--
--------


"Gary''s Student" wrote:

Text to columns is the best way, but the following UDF will do the same thing:

Public Function items(r As Range, whichone As Integer)
ary = Split(r.Value, ",")
items = ary(whichone - 1)
End Function

So with your data in A1, enter:

=items(A1,1) =items(A1,2) =items(A1,3) =items(A1,4)

in some row
--
Gary''s Student - gsnu200804


"Hardworker" wrote:

hi,

is it possible to convert the contents of cells to rows eg. i have rows of
cells which contains numbers seperated by a comma (542212, 542213, 542214
etc).

Is there a way of doing this without using the text to column first?

contents of cells to rows
 

Hi Radiolistener,

I think u misunderstanding. I need the contents of the cell to be seperated
into rows, not columns. The way i did it was to use text to columns and then
transpose them. I want to know if there is a way to do it without using the
text to column.

eg (52221, 52222, 52223) What i would like is:
A 52221
B 52222
C 52223

Is this possible? Is this what you sent me?
--
--------


"Radiolistener" wrote:

Hi Hardworker,

Say you make a macro enabled worksheet. In A2 put "5,6,7,8" in Column C2,
go into developer and pick insert, add a button.

In the button code, put the following:

Sub Button1_Click()
Dim texter As String

Dim datatocolumn() As String

texter = Sheet1.Range("A2")
datatocolumn = Split(texter, ",")
Dim i As Integer

i = 5

Dim item As Variant
For Each item In datatocolumn

Sheet1.Range("A" & Trim(Str(i))).Value = item
i = i + 1

Next item


End Sub


That would do it, but I'm really not sure why you wouldn't want to use text
to column.

In any event, I hope that this is the answer you are looking for.

John



"Hardworker" wrote:

hi,

is it possible to convert the contents of cells to rows eg. i have rows of
cells which contains numbers seperated by a comma (542212, 542213, 542214
etc).

Is there a way of doing this without using the text to column first?

contents of cells to rows
 

Try this:

Sub Button1_Click()
Dim texter As String

Dim datatocolumn() As String

texter = Sheet1.Range("A2")
datatocolumn = Split(texter, ",")
Dim i As Long

i = Asc("B")

Dim item As Variant
For Each item In datatocolumn

Sheet1.Range(Chr(i) & "2").Value = item
i = i + 1

Next item


End Sub

"Hardworker" wrote:

Hi Radiolistener,

I think u misunderstanding. I need the contents of the cell to be seperated
into rows, not columns. The way i did it was to use text to columns and then
transpose them. I want to know if there is a way to do it without using the
text to column.

eg (52221, 52222, 52223) What i would like is:
A 52221
B 52222
C 52223

Is this possible? Is this what you sent me?
--
--------


"Radiolistener" wrote:

Hi Hardworker,

Say you make a macro enabled worksheet. In A2 put "5,6,7,8" in Column C2,
go into developer and pick insert, add a button.

In the button code, put the following:

Sub Button1_Click()
Dim texter As String

Dim datatocolumn() As String

texter = Sheet1.Range("A2")
datatocolumn = Split(texter, ",")
Dim i As Integer

i = 5

Dim item As Variant
For Each item In datatocolumn

Sheet1.Range("A" & Trim(Str(i))).Value = item
i = i + 1

Next item


End Sub


That would do it, but I'm really not sure why you wouldn't want to use text
to column.

In any event, I hope that this is the answer you are looking for.

John



"Hardworker" wrote:

hi,

is it possible to convert the contents of cells to rows eg. i have rows of
cells which contains numbers seperated by a comma (542212, 542213, 542214
etc).

Is there a way of doing this without using the text to column first?

contents of cells to rows
 

The error message means that the macro is in the wrong place. First erase
the macro and then:


User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=items(A1,1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs




--
Gary''s Student - gsnu200804


"Hardworker" wrote:

Hi Gary,

thanks for the help. i am not too familiar with coding in excel. Do i just
paste this in the VB Editor?

I think i did what you said i should but it gives me a messgae of "#NAME?"

What am i doing wrong?

--
--------


"Gary''s Student" wrote:

Text to columns is the best way, but the following UDF will do the same thing:

Public Function items(r As Range, whichone As Integer)
ary = Split(r.Value, ",")
items = ary(whichone - 1)
End Function

So with your data in A1, enter:

=items(A1,1) =items(A1,2) =items(A1,3) =items(A1,4)

in some row
--
Gary''s Student - gsnu200804


"Hardworker" wrote:

hi,

is it possible to convert the contents of cells to rows eg. i have rows of
cells which contains numbers seperated by a comma (542212, 542213, 542214
etc).

Is there a way of doing this without using the text to column first?