hi. i want to merge many rows in to one cell. there are many names on
companies i want to display in a flowchart and want to be able to take the
individual rows and make them one cell so i can wrap the cell and format to
the left

-- is there a way to do that?

=A1&char(10)&A2&char(10)&A3&char(10) ... etc.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"feliciamm" wrote in message
...
hi. i want to merge many rows in to one cell. there are many names on
companies i want to display in a flowchart and want to be able to take the
individual rows and make them one cell so i can wrap the cell and format
to
the left

-- is there a way to do that?

Formula as RagDyer suggests or try a user defined function to get all in one
cell, space delimited.

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

Usage is: =ConCatRange(a1:a20)

If you're not familiar with VBA and macros/functions, see David McRitchie's site
for more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

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 your workbook.

In a cell enter the formula as shown above in Usage is:


Gord Dibben MS Excel MVP


On Tue, 13 May 2008 16:31:01 -0700, feliciamm
wrote:

hi. i want to merge many rows in to one cell. there are many names on
companies i want to display in a flowchart and want to be able to take the
individual rows and make them one cell so i can wrap the cell and format to
the left

-- is there a way to do that?