Line Break in Cell Number Format

[briancrosier]

I would like to format a number so that it reads as follows:

Lump Sum
$5,000.00

I know that using custom formatting I can add the text, but I would like to
show the result in two lines. Is there a way to add a line break in custom
formatting?

 

[Sean Timmons]

use ALTand 0010 from your number pad, not the top row of your keyboard, to
enter a line break in format cells.

 

[Rick Rothstein]

Set the cell's Format to "Wrap Text" on the Alignment Tab and then in your
Custom Format back on the Number Tab, press Ctrl+J wherever you want the new
line to be inserted (you won't see the effect in the Custom Format field,
but it will show in the cell itself.

 

[Rick Rothstein]

As it turns out, you can see the effect of the Ctrl+J in the Custom Format
field... use the up/down arrows to see each separate line.

 

[Dave Peterson]

Just to add to Rick and Sean's reply...

Format|cells
On the Number tab
Custom category
"Lump Sum"(ctrl-j)$#,##0.00

Then on the Alignment tab
Uncheck Wrap text (temporarily)
Check Shrink to fit
Check Wrap text

You will have to adjust the rowheight manually.

If you don't have shrink to fit checked, you'll see #### if you shrink the
columnwidth--even when it looks like there's plenty of room for the wrapped text
to show up.

 

[Rick Rothstein]

Hmm! I hadn't tried shrinking the width... that's weird.

 

[Rick Rothstein]

Never mind... I see why. Excel calculates the width of the text as if there
were no Line Feed in it (well, almost as there seems to be an extra space or
two being counted as well)... if the Column Width is set less that this
value, Excel thinks text no longer fits. I'm surprised it does this as there
are API functions available which correctly measure the width of text
containing Line Feeds... you would think since Microsoft wrote those API
functions, it would have used them in its other products (such as Excel
maybe<g>).

--
Rick (MVP - Excel)

Hmm! I hadn't tried shrinking the width... that's weird.

 

[Dave Peterson]

They're smart enough to do it with plain old text, but not through a custom
number format.

Never mind... I see why. Excel calculates the width of the text as if there
were no Line Feed in it (well, almost as there seems to be an extra space or
two being counted as well)... if the Column Width is set less that this
value, Excel thinks text no longer fits. I'm surprised it does this as there
are API functions available which correctly measure the width of text
containing Line Feeds... you would think since Microsoft wrote those API
functions, it would have used them in its other products (such as Excel
maybe<g>).

 

[Sean Timmons]

I'd say we all learned something new on this one. Hope our original poster
did as well!