Insert Carriage Return in Custom Format: Possible? How?

[UniMord]

Is there any way to sneak a Carriage Return into a Custom Format?

For example, I'd like the format to show: $#,##0(CR HERE)"(I & B)"

 

[UniMord]

V("Thanks Greg & Glenn!") Drinks at the pub for all!

Once again, V()

 

[Greg Truby]

UniMord - did you see my subsequent post? You can do what you originally wanted to do -- just use a smidgeon of VBA and it's easy enough.

 

[UniMord]

That code leaves the entries as strings. I will still need to parse out the numbers and sum them up. No?

Also, how do I apply this function to the column in question?

 

[Greg Truby]

No, the code I posted is altering the NumberFormat property of the cells exactly as you first were thinking. So the items in the cells are actually just numbers. [Run the last one there on a cell with just a number in it and look at your formula bar.] In addition to the reasons you first cited (ease of summing), using number formatting is also better because you could then apply your parenthetical descriptions on cells containing formulas.

 

[Greg Truby]

...how do I apply this function to the column in question?

Just drop that code into a standard module. Skip back to Excel and select the cell you want to modify. Run the macro and input the description you want when prompted. If you select more than one cell, all the cells will have the same description applied.

 

[UniMord]

It works beautifully, with one small flaw, in that it forces the use a very wide column, upon threat of ###################ing the cell.

Why is that, and can it be fixed?

 

[Greg Truby]

I'm not sure why, but even with wrap text turned on, Excel seems to be estimating the needed column width as the width of the number and the subsequent description. If the column is not wide enough for it to fit as though wrap text were false, it's showing ###### even though it ought not to. (Guess the developers didn't anticipate us sneaking CR's into number formats.)

 

[GlennUK]

Another go ( I didn't know about the blank cells ):

=SUM(VALUE("0"&LEFT(A1:A6,SEARCH(CHAR(10),A1:A6&CHAR(10))-1)))

 

[UniMord]

That is a very clever way of forcing the formula to work! I'll have to add that to my bag of tricks.

Unfortunately, it doesn't work in my particular case since all the cells with entries start with a $ sign, and adding the "0" won't work.

 

[Greg Truby]

Nicely done, Glenn. I had played with trying to add a zero or multiply by one or coerce with a "--" to handle the "" from blank cells. I can't believe I didn't think to concatenate a "0" on the left. Again, kudos.