| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenging formatting formula
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!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenging formatting formula
You get spaces because your IF() function returns spaces.
To get "multiline" results you need to enable "Wrap text" in "Format Cells - Alignment"... -- Regards, Luc. "Festina Lente" "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!! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenging formatting formula
Thank you for your input.
I had already had Wrap text configured prior to running the formulas. I would appreciate any further suggestions. Thanks! "PapaDos" wrote: You get spaces because your IF() function returns spaces. To get "multiline" results you need to enable "Wrap text" in "Format Cells - Alignment"... -- Regards, Luc. "Festina Lente" "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!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenging formatting formula
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" wrote: You get spaces because your IF() function returns spaces. To get "multiline" results you need to enable "Wrap text" in "Format Cells - Alignment"... -- Regards, Luc. "Festina Lente" "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!! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenging formatting formula
Are you sure it is enabled for the cell containing your formula ?
-- Regards, Luc. "Festina Lente" "robnet" wrote: Thank you for your input. I had already had Wrap text configured prior to running the formulas. I would appreciate any further suggestions. Thanks! "PapaDos" wrote: You get spaces because your IF() function returns spaces. To get "multiline" results you need to enable "Wrap text" in "Format Cells - Alignment"... -- Regards, Luc. "Festina Lente" "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!! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenging formatting formula
I double checked. Yes It is still enabled for the cells containing the
formula and the results cell. "PapaDos" wrote: Are you sure it is enabled for the cell containing your formula ? -- Regards, Luc. "Festina Lente" "robnet" wrote: Thank you for your input. I had already had Wrap text configured prior to running the formulas. I would appreciate any further suggestions. Thanks! "PapaDos" wrote: You get spaces because your IF() function returns spaces. To get "multiline" results you need to enable "Wrap text" in "Format Cells - Alignment"... -- Regards, Luc. "Festina Lente" "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!! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenging formatting formula
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 Singapore http://savefile.com/projects/236895 xdemechanik --- "robnet" wrote: I double checked. Yes It is still enabled for the cells containing the formula and the results cell. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenging formatting formula
oops, this part
.. to pick up the Alt+Enters should read: .. to insert the Alt+Enters -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenging formatting formula
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!! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenging formatting formula
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!! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenging formatting formula
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. "Max" wrote: 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 Singapore http://savefile.com/projects/236895 xdemechanik --- "robnet" wrote: I double checked. Yes It is still enabled for the cells containing the formula and the results cell. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenging formatting formula
Give this alternative a try -- it might just work for what you have over the
In say, B7, with B7 formatted to wrap text: =SUBSTITUTE(TRIM(A7&" "&A8&" "&A9)," ",CHAR(10)) Adapt the part within TRIM to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "robnet" wrote: 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. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Challenging formatting formula
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!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Challenging Formula. Need help with writing a formula | Excel Worksheet Functions | |||
| challenging formula(for me), counting days between dates for multipleyears | Excel Worksheet Functions | |||
| Answers needed for challenging formula | Excel Worksheet Functions | |||
| Challenging Problem | Excel Discussion (Misc queries) | |||
| Here's a challenging question for you... | Excel Discussion (Misc queries) | |||
Linear Mode
