Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change list of cells to paragrph form
Is there a way to change a list of cells to paragraph form in 1 merged cell?
Example: Blue Black Red Orange Purple Green Convert to: Blue, Black, Red, Orange, Purple,Green. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change list of cells to paragrph form
=CONCATENATE(A1,",",A2,",",A3) up to A6
Note: if any blank cells you will get extra commas. e.g. blue,,red,,orange, green if black and purple are missing. I prefer using a user defined function like the following which ignores blank cells. Function ConCatRange(CellBlock As Range) As String Dim cell As Range Dim sbuf As String For Each cell In CellBlock If Len(cell.text) 0 Then sbuf = sbuf & cell.text & "," Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to Excel window. Enter the formula in a helper cell as =ConCatRange(A1:A6) Gord Dibben MS Excel MVP On Thu, 19 Jul 2007 16:56:01 -0700, dford wrote: Is there a way to change a list of cells to paragraph form in 1 merged cell? Example: Blue Black Red Orange Purple Green Convert to: Blue, Black, Red, Orange, Purple,Green. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change list of cells to paragrph form
That worked great. Thanks for the help.
"Gord Dibben" wrote: =CONCATENATE(A1,",",A2,",",A3) up to A6 Note: if any blank cells you will get extra commas. e.g. blue,,red,,orange, green if black and purple are missing. I prefer using a user defined function like the following which ignores blank cells. Function ConCatRange(CellBlock As Range) As String Dim cell As Range Dim sbuf As String For Each cell In CellBlock If Len(cell.text) 0 Then sbuf = sbuf & cell.text & "," Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to Excel window. Enter the formula in a helper cell as =ConCatRange(A1:A6) Gord Dibben MS Excel MVP On Thu, 19 Jul 2007 16:56:01 -0700, dford wrote: Is there a way to change a list of cells to paragraph form in 1 merged cell? Example: Blue Black Red Orange Purple Green Convert to: Blue, Black, Red, Orange, Purple,Green. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change data in cells of an existing saved form- | Excel Discussion (Misc queries) | |||
Can a standard data entry form be used to change formats of cells? | Excel Discussion (Misc queries) | |||
how can I make a form number change everytime the form is opened | Excel Worksheet Functions | |||
change info in other cells when i change a number in a drop list? | Excel Discussion (Misc queries) | |||
I Need to change reference sheet for all cells on a form | Excel Discussion (Misc queries) |