43 replies to this topic

[lennykravitz2004]

Big Country, on 7/14/11 5:39pm, said:

Wish I could take the credit, but it was the result of a successful Google search and making some minor modifications to the samples presented.

But, this may well be something that I can use in the future, especially in some FF related research.

Ditto (from a few weeks ago).  

[rajncajn]

I've got multiple tabs in a spreadsheet, each containing multiple rows of similar data, but each cell, may have specific formatting. is there a way to add a tab that shows a compiled, sortable list that carries the formatting and updates for any changes I make from the source data? I've tried doing an "=" and selecting the data from the source. That works for everything but the formatting.

[The Irish Doggy]

rajncajn, on 2/13/12 2:39pm, said:

I've got multiple tabs in a spreadsheet, each containing multiple rows of similar data, but each cell, may have specific formatting. is there a way to add a tab that shows a compiled, sortable list that carries the formatting and updates for any changes I make from the source data? I've tried doing an "=" and selecting the data from the source. That works for everything but the formatting.

Not sure if this will help, but...

I think you have to use some kind of macro to copy formats.  Otherwise, you have to format the summary tab.  So long as you aren't mixing formats as you update data, it will keep the formating you assign in the summary tab.

I use the vlookup and hlookup command quite a bit for this sort of task.

[rajncajn]

Using the "concatenate" function I am combining multiple cells into one. However between each cell, which represents a line of text, I have added a character return so rather than having one long line of information it looks more like the example below in the cell:

SD-V-324
SEC DRAINAGE EDUC
BILGE SUCT PRESS GA ROOT

My issue now, though is that since there are several cells that are being combined, I'm ending up with ghost character returns within the cell. So the cell in all actuality is 20 lines deep rather than just the 3 you see above.. I need to get rid of those ghost returns. i've tried the "clean" function, but that takes them all out. I've also tried the "trim" function, but that only takes out leading & trailing spaces.

[gbpfan1231]

rajncajn, on 26 April 2012 - 12:52 PM, said:

Using the "concatenate" function I am combining multiple cells into one. However between each cell, which represents a line of text, I have added a character return so rather than having one long line of information it looks more like the example below in the cell:

SD-V-324
SEC DRAINAGE EDUC
BILGE SUCT PRESS GA ROOT

My issue now, though is that since there are several cells that are being combined, I'm ending up with ghost character returns within the cell. So the cell in all actuality is 20 lines deep rather than just the 3 you see above.. I need to get rid of those ghost returns. i've tried the "clean" function, but that takes them all out. I've also tried the "trim" function, but that only takes out leading & trailing spaces.

I am not sure I understand - why are you adding a character return?

[rajncajn]

gbpfan1231, on 26 April 2012 - 01:01 PM, said:

I am not sure I understand - why are you adding a character return?

So the text displays & prints like this:

SD-V-324
SEC DRAINAGE EDUC
BILGE SUCT PRESS GA ROOT

Rather than this:

SD-V-324 SEC DRAINAGE EDUC BILGE SUCT PRESS GA ROOT


Each line from the first example is taken from a cell. There are 16 cells in each row, some have text, some don't.

Edited by rajncajn, 26 April 2012 - 01:11 PM.

[stevegrab]

I assume the empty cells vary by row, if not could they be rearranged so that you only have the 3 cells with data one after each other, then only concatenate those 3 cells.

I'm not that good with Excel (most of what you guys are talking about is over my head) but I'm a computer programmer. My angle here would be to only add the CR when the cell is not empty.

[rajncajn]

stevegrab, on 26 April 2012 - 02:08 PM, said:

I assume the empty cells vary by row, if not could they be rearranged so that you only have the 3 cells with data one after each other, then only concatenate those 3 cells.

I'm not that good with Excel (most of what you guys are talking about is over my head) but I'm a computer programmer. My angle here would be to only add the CR when the cell is not empty.

Correct, in a row of 16 cells, some have data, some do not which is intentional so that the data forms a specific pattern when it is printed. It would look something like this in the spreadsheet:


|this |is   |how  |it   |would|look |

That's 6 cells with text with each "|" representing a cell border. So when you concatenate to gain the result I'm looking for you would end up with this in a single cell:

this
is
how
it
would
look

now the next row may only have 3 lines of text resulting in:

this
is
how

Where the issue comes in is that I am drawing from 16 cells across the row to build the one. So where there is just a blank cell you end up with this hidden character trailing the text. So, if you could see what is actually in the cell, you are getting this (the "+" representing the hidden carriage return):

this
is
how
it
would
look
+
+
+
+
+
+
+
+
+
+

Edited by rajncajn, 26 April 2012 - 02:37 PM.

[stevegrab]

Sorry wish I knew more about Excel maybe I could help. I'm used to my own programming environment and never learned a lot of the complex stuff in Excel.

I looked at the concatenate command some and don't see any way to prevent adding the carriage returns (CR).  Maybe you can use some other commands on this cell to remove the last character if it is a CR. But I don't know what those commands would be or how you'd test for the CR.

The more complex things I've seen done in Excel usually involve macros, you may need to start brushing up on those.

[Big Country]

Get rid of the carriage return in the cells. Use the following in the cell that you want to concatenate - this is built assuming that your data is in columns A thru P (you said you have 16 columns of data)- you most format the cell you put the formula in to wrap text:

=IF(A1="","",A1&CHAR(10))&IF(B1="","",B1&CHAR(10))&IF(C1="","",C1&CHAR(10))&IF(D1="","",D1&CHAR(10))&IF(E1="","",E1&CHAR(10))&IF(F1="","",F1&CHAR(10))&IF(F1="","",F1&CHAR(10))&IF(G1="","",G1&CHAR(10))&IF(H1="","",H1&CHAR(10))&IF(I1="","",I1&CHAR(10))&IF(J1="","",J1&CHAR(10))&IF(K1="","",K1&CHAR(10))&IF(L1="","",L1&CHAR(10))&IF(M1="","",M1&CHAR(10))&IF(N1="","",N1&CHAR(10))&IF(O1="","",O1&CHAR(10))&IF(P1="","",P1)

[rajncajn]

Big Country, on 26 April 2012 - 03:31 PM, said:

Get rid of the carriage return in the cells. Use the following in the cell that you want to concatenate - this is built assuming that your data is in columns A thru P (you said you have 16 columns of data)- you most format the cell you put the formula in to wrap text:

=IF(A1="","",A1&CHAR(10))&IF(B1="","",B1&CHAR(10))&IF(C1="","",C1&CHAR(10))&IF(D1="","",D1&CHAR(10))&IF(E1="","",E1&CHAR(10))&IF(F1="","",F1&CHAR(10))&IF(F1="","",F1&CHAR(10))&IF(G1="","",G1&CHAR(10))&IF(H1="","",H1&CHAR(10))&IF(I1="","",I1&CHAR(10))&IF(J1="","",J1&CHAR(10))&IF(K1="","",K1&CHAR(10))&IF(L1="","",L1&CHAR(10))&IF(M1="","",M1&CHAR(10))&IF(N1="","",N1&CHAR(10))&IF(O1="","",O1&CHAR(10))&IF(P1="","",P1)

That got rid of all of them, I only want to remove those at the end.

[Big Country]

rajncajn, on 27 April 2012 - 08:58 AM, said:

That got rid of all of them, I only want to remove those at the end.

Did you set the cell you put the formula in to wrap text?

The formula adds a line break to the end of every cell with anything in it, and ignores (with no line break) any cell that is blank, so that there are no extraneous line breaks.

It is working just as you requested in post 33 on my machine, but, if you do not have the cell formatted to wrap text, it may not display as you want.

[rajncajn]

Big Country, on 27 April 2012 - 09:52 AM, said:

Did you set the cell you put the formula in to wrap text?

The formula adds a line break to the end of every cell with anything in it, and ignores (with no line break) any cell that is blank, so that there are no extraneous line breaks.

It is working just as you requested in post 33 on my machine, but, if you do not have the cell formatted to wrap text, it may not display as you want.

Holy crap, that did work. One problem though it that if there are blank cells in the middle of the data that are needed then it wipes them out as well. For example:

AFFF-V-9 (1-309-1)
CONCENTRATE CONTROL VALVE
INTERLOCKED W/AFF-V-6(1-313-1)
SEAWATER CONTROL VALVE


ELEC REMOTE OPERATORS AT
(4-295-1)(4-276-1)(4-224-1)(3-405-1)
(3-284-2)(2-446-2)(2-445-2)(2-442-1)
(2-428-2)(2-401-1)(2-400-1)(2-379-2)
(2-353-1)(2-299-1)(2-260-2)(1-399-2)
(1-399-1)(1-394-2)(1-394-1)(1-390-2)
(1-389-1)(1-333-2)(1-325-1)(1-299-1)
(1-292-1)(1-278-1)(01-396-1)
(01-396-3)(01-396-5)
MECH LOCAL OPERATOR AT
(1-308-5) AFFF-V-30


That would all be in one cell, but lines 5 & 6 (cell 5 & 6) are left blank intentionally so that there spaces designed into the data.

Just for info, I'm not just being anal about the extra carriage returns at the end. This data will be extracted to a database used to build a label. The height of the label builds depending on the lines of text needed. So in essence a label with only 3 lines of text ends up the same size as the label with 18 lines of text which creates a lot of extra work & wasted material. The material used is quite pricey & the process is a bit time consuming. We have to make it as easy & cost efficient as possible so that it can be done aboard the ship.

[rajncajn]

Alright, one of the IT guys here finally came up with the solution. Here's the formula if anybody wants to see:

=LEFT(C2,LOOKUP(LEN(C2),ROW(INDIRECT("1:"&LEN(C2)))/(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1)<>CHAR(10))))

[stevegrab]

Rajn,

Glad you found the answer, those IT guys (if they know/use Excel) might have made it a little more complex, but if it works that all that matters.

below is my original post (before I saw you got it solved)
Sounds like you only want to remove the extra CR that occur at the end of your result, not blank ones in the middle. As Big Country said, his formula is basically doing this "The formula adds a line break to the end of every cell with anything in it, and ignores (with no line break) any cell that is blank, so that there are no extraneous line breaks."

Maybe you need to build your result cell as done initially, then write a formula to go trough and remove the last character as long as it is a CR (that is the CHAR(10) piece from what Big Country showed. Maybe combinations of the FIND and RIGHT or LEFT commands would get that done.

[CaP'N GRuNGe]

rajncajn, on 27 April 2012 - 11:03 AM, said:

Alright, one of the IT guys here finally came up with the solution. Here's the formula if anybody wants to see:

=LEFT(C2,LOOKUP(LEN(C2),ROW(INDIRECT("1:"&LEN(C2)))/(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1)<>CHAR(10))))

Some of these excel formulas are so complex they look like they could open up a wormhole.

[rajncajn]

stevegrab, on 27 April 2012 - 11:06 AM, said:

Sounds like you only want to remove the extra CR that occur at the end of your result, not blank ones in the middle. As Big Country said, his formula is basically doing this "The formula adds a line break to the end of every cell with anything in it, and ignores (with no line break) any cell that is blank, so that there are no extraneous line breaks."

Maybe you need to build your result cell as done initially, then write a formula to go trough and remove the last character as long as it is a CR (that is the CHAR(10) piece from what Big Country showed. Maybe combinations of the FIND and RIGHT or LEFT commands would get that done.

Yes, that's exactly how I was trying to accomplish it. I just don't have a very good working knowledge of those three commands just yet.

[rajncajn]

In copying a table from Excel to Access, how do I get Access to recognize the carriage returns that are within a cell? My data table in Excel has carriage returns within each cell, but when I import it to my data table in Access they no longer show up.

[lennykravitz2004]

rajncajn, on 09 May 2012 - 08:48 AM, said:

In copying a table from Excel to Access, how do I get Access to recognize the carriage returns that are within a cell? My data table in Excel has carriage returns within each cell, but when I import it to my data table in Access they no longer show up.

http://support.microsoft.com/kb/115576

Not sure how handy you are with stuff like this...

ETA:
A "hard return" in an EXCEL cell is the line feed character (Chr(10)). A
"hard return" in an ACCESS field is the combination of the carriage return
and line feed characters (Chr(13) & Chr(10)). The square box you see in the
ACCESS field is the line feed character. What you need to do is run an
update query on the table so that you convert the line feed character to the
combination:

UPDATE YourTableName
SET YourFieldName = Replace(YourFieldName, Chr(10), Chr(13) & Chr(10));

Note that you want to run this update query ONLY ONCE on the table after you
import the data. Otherwise, you'll end up with multiple,consecutuve Chr(13)
characters in your data.

http://www.pcreview....s-t3558252.html

Edited by lennykravitz2004, 09 May 2012 - 10:32 AM.