Insert Carriage Return in Custom Format: Possible? How?

UniMord

Active Member
Joined
May 6, 2002
Messages
311
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)"
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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:
Code:
=TEXT(A1,"$#,##0")&CHAR(10)&"(I & B)"
and change the cell formatting to have wrap on.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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)))}
 
Upvote 0
So what is the content of cell I2 for example?

Anyway, how about this:
Code:
=SUM(VALUE(LEFT(I2:I52,SEARCH(CHAR(10),I2:I52)-1)))
entered using Ctrl-Shift-Enter instead of Enter.
 
Upvote 0
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?
 
Upvote 0
Code:
=SUM(VALUE(LEFT(I4:I45,SEARCH(CHAR(10),I4:I45&CHAR(10))-1)))
 
Upvote 0
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
 
Upvote 0
LOL - I so enjoyed your array formulas that I forgot to try out a rather simple answer...
Code:
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...
Code:
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
 
Upvote 0

Forum statistics

Threads
1,211,789
Messages
6,103,986
Members
447,889
Latest member
birdman15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top