Challenging formatting formula

[robnet]

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!!

 

[PapaDos]

You get spaces because your IF() function returns spaces.
To get "multiline" results you need to enable "Wrap text" in "Format Cells
-> Alignment"...

 

[robnet]

Thank you for your input.

I had already had Wrap text configured prior to running the formulas.

I would appreciate any further suggestions.
Thanks!

 

[robnet]

Also, to be cleare on teh formula, yes it returns a space, but only if the
argument is false. The formula to concatenate does not include those "Blank"
cells when it calculates the results.

 

[PapaDos]

Are you sure it is enabled for the cell containing your formula ?

 

[robnet]

I double checked. Yes It is still enabled for the cells containing the
formula and the results cell.

 

[Max]

Perhaps you mean something like this instead:
=A7&CHAR(10)&A8&CHAR(10)&A9 ....
with " &CHAR(10)& " inserted in-between to pick up the Alt+Enters

 

[Max]

oops, this part

.. to pick up the Alt+Enters

should read:

 

[Gord Dibben]

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

 

[robnet]

I was hoping it would work, but it didn't. I got very strange formatting
results...I really want to thank you for your help! I learned something.

 

[robnet]

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

 

[Max]

Give this alternative a try -- it might just work for what you have over there:

In say, B7, with B7 formatted to wrap text:
=SUBSTITUTE(TRIM(A7&" "&A8&" "&A9)," ",CHAR(10))
Adapt the part within TRIM to suit.

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP