Change list of cells to paragrph form

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
=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
 
That worked great. Thanks for the help.

Gord Dibben said:
=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
Click to expand...
 
Back
Top