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

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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.
 

UniMord

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

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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

Active Member
Joined
May 6, 2002
Messages
311

ADVERTISEMENT

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

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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.
 

UniMord

Active Member
Joined
May 6, 2002
Messages
311

ADVERTISEMENT

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

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Code:
=SUM(VALUE(LEFT(I4:I45,SEARCH(CHAR(10),I4:I45&CHAR(10))-1)))
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,013
Members
412,304
Latest member
citrus
Top