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?

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

"briancrosier" wrote:

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?

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 (MVP - Excel)


"briancrosier" wrote in message
...
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?

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"briancrosier" wrote in message
...
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?

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.

briancrosier wrote:

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?

--

Dave Peterson

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

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
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.

briancrosier wrote:

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?

--

Dave Peterson

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)


"Rick Rothstein" wrote in message
...
Hmm! I hadn't tried shrinking the width... that's weird.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
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.

briancrosier wrote:

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?

--

Dave Peterson

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



Rick Rothstein wrote:

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)

"Rick Rothstein" wrote in message
...
Hmm! I hadn't tried shrinking the width... that's weird.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
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.

briancrosier wrote:

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?

--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote:

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



Rick Rothstein wrote:

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)

"Rick Rothstein" wrote in message
...
Hmm! I hadn't tried shrinking the width... that's weird.

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
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.

briancrosier wrote:

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?

--

Dave Peterson


--

Dave Peterson