Change list of cells to paragrph form

[Guest]

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.

 

[Gord Dibben]

=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 Insert>Module. 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

 

[Guest]

That worked great. Thanks for the help.

=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 Insert>Module. 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