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

 

[Greg Truby]

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.

A small tweak to Glenn's formula does the trick: <ul>[*]{=SUM(VALUE("0"&SUBSTITUTE(LEFT(RngToSum,SEARCH(CHAR(10),RngToSum&CHAR(10))-1),"$","")))}[/list]

 

[UniMord]

Voila! It works like a charm! Once again, thank you Greg & Glenn.

 

[GlennUK]

Hooray!!!! That was a tough one

 

[Greg Truby]

Well, I must say that both y'all impressed me. UniMord, do you know how exceedingly rare it is for someone with only a few dozen posts here to even know what an array formula is? Let alone post a formula of the complexity you did. And then to see the code for v() and understand how to apply it? I don't know where you learned your Excel, but welcome!

 

[UniMord]

Thanks for the compliment.

 

[rstout]

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

Not sure why it works, but typing CTRL J while creating a customized format will force a carriage return. You do need to make sure Wrap Text is selected to get the desired effect.