contents of cells to rows

[Hardworker]

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?

 

[Gary''s Student]

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

 

[Radiolistener]

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]

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?

 

[Hardworker]

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]

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

 

[Gary''s Student]

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/WritingFunctionsInVBA.aspx
for specifics on UDFs