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)"

 

[GlennUK]

Not that I know of. In fact when numbers are formatted, even though you make them look like strings ( as in your case ), there is a lot you cannot do in the way of formatting, for example, wrapping. If you want to do this kind of thing, then convert your number into a text, and add in the (CR) and other stuff, and you'll be able to have your cells looks how you want, except the entries are no longer numeric.

To add a (CR) to your number, use a formula like this:

=TEXT(A1,"$#,##0")&CHAR(10)&"(I & B)"

and change the cell formatting to have wrap on.

 

[UniMord]

The very reason I was trying to do this with Custom Formatting was to keep the contents as numbers so I could get a total at the bottom of the column.

If I were to enter the information as text, my purpose would be defeated, unless there's a workable way to total it.

Any ideas?

 

[GlennUK]

I recommended having a formula to convert your numbers to text, which would leave the original numbers intact. Use the original numbers for summing. Hide the column of original numbers if you don't want to see them.

 

[UniMord]

These are the original numbers. Is it possible to come up with an array formula which will extract just the number part, & sum them up? I tried the following - unsuccessfully:

{=SUM(LEFT(I2:I52,SEARCH(CHAR(10),I2:I52)))}

 

[GlennUK]

So what is the content of cell I2 for example?

Anyway, how about this:

=SUM(VALUE(LEFT(I2:I52,SEARCH(CHAR(10),I2:I52)-1)))

entered using Ctrl-Shift-Enter instead of Enter.

 

[UniMord]

I wouldn't normally do it this way, but there is an "Etc." column on this spreadsheet for the 2 or 3 odd values that don't belong in any of the other columns. It is bad enough, presentation-wise to have this column, I didn't want to introduce yet another column to keep the comments separate, even though that's the right way to do it, in terms of spreadsheet integrity.

The values are [Dollar amount][CR][Description]. such as the one I'd mentioned earlier:
$250,000
(I & B)

Your formula would have worked if every cell contained a number and a CR. I entered the following unwieldy but functional variation on your formula to avoid the dreaded #VALUE!:

{=SUM(IF(ISERR(VALUE(LEFT(I4:I45,SEARCH(CHAR(10),I4:I45)-1))),0,VALUE(LEFT(I4:I45,SEARCH(CHAR(10),I4:I45)-1))))}

Do you have a more elegant solution?

 

[GlennUK]

=SUM(VALUE(LEFT(I4:I45,SEARCH(CHAR(10),I4:I45&CHAR(10))-1)))

 

[Greg Truby]

Glenn, I'm getting a value error with that due to VALUE("") failing on blanks.

UniMord (a) nice formula. (b) I was able to shorten it up slightly using the v() udf. <ul>[*]{=SUM(IF(ISERR(v(VALUE(LEFT(RngToSum,SEARCH(CHAR(10),RngToSum)-1)))),0,v()))}[/list]
HTH

 

[Greg Truby]

LOL - I so enjoyed your array formulas that I forgot to try out a rather simple answer...

Sub foof()
    Selection.NumberFormat = "#,##0 " & Chr(10) & """(I & B)"""
End Sub

Sub offo()
    With Selection
        .NumberFormat = "#,##0 " & Chr(10) & """(drinks at the pub)"""
        .WrapText = True
    End With
End Sub

{edit}
Perhaps a little more useful...

Sub InputDesc()
    Dim strDesc As String, strFormat As String
    
    strDesc = InputBox("And the description is?", "Item Desc")
    
    strFormat = "#,##0 " & Chr(10) & """(" _
                & strDesc & ")"""
    
    With Selection
        .NumberFormat = strFormat
        .WrapText = True
        .RowHeight = 26
    End With

End Sub