Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate cells, replacing blanks with cell contents from other rows | Excel Worksheet Functions | |||
compare the contents of one range of cells with the contents of a. | Excel Discussion (Misc queries) | |||
how to sum contents of duplicate rows | Excel Discussion (Misc queries) | |||
Stop rows from being deleted whilst allowing cells contents to cha | Excel Discussion (Misc queries) | |||
How do I center the contents of columns or rows? | Excel Discussion (Misc queries) |