First off.........save a backup of your workbook.

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 explained below.


Gord Dibben MS Excel MVP

On Wed, 18 Jul 2007 17:48:02 -0700, robnet
wrote:

Thank you Gord. How do I use this. I am not that informed...
Thanks!

"Gord Dibben" wrote:

You want all those cells' data in one cell?

Use this UDF 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

=ConCatRange(A7:A100) Format cell to wrap text and row to autofit.

Note: that many cells may OD the row height limit which is 409


Gord Dibben MS Excel MVP

On Wed, 18 Jul 2007 12:54:01 -0700, robnet
wrote:

I have multi-lined text data in source cells. There is a Y/N chart for that
data with an if-then "=IF('3. VPN Configuration'!C10="Y",'4. Environment IP
Addresses'!$B6," ")" which basically states if the cell = Y, then put cell
data on another sheet, which it does just fine. (thanks to you guys).

There are numerous blank rows in each column, so I am doing a simple
concatenate twice (It does not work with more that 60 entries), then
concatenate again to get the final list. Here is the formula:
=A7&A8&A9&A10&A11&A12&A13&A14&A15&A16&A17&A18&A19 &A20&A21&A22&A23&A24&A25&A26&A27&....
then the last part: =A77&A78 (concatonates the two concatonated rows)

This is the result: (yuck!)

10.98.144.0/22
10.98.184.0/22
10.98.192.0/22 10.98.80.168

10.98.080.0/22 10.98.240.0/24 10.98.230.0/24
10.98.040.0/22 10.98.3.41(iDMZ)
10.98.3.40(iDMZ)
10.98.230.118
10.98.230.200
10.98.230.209
10.98.230.112 10.98.080.0/22 10.98.080.0/22
10.98.40.0/22
10.98.020.0/24 10.98.230.0/24 10.98.144.0/22 10.98.144.0/22
10.98.240.0/24

In the source cells, each line item is entered with "Alt+Enter" and there
are no spaces anywhere in the source.

I cannot determine how to get a final presentation of the concatenated data
to look like this: (not specifically representative of the above data)
10.98.3.41(iDMZ)
10.98.3.40(iDMZ)
10.98.230.118
10.98.230.200
10.98.230.209
10.98.230.112
etc...


Your ideas are GREATLY appreciated!!
Thanks!!