Custom formatting 1

[StephenA]

I have been using the Custom format for several years such as entering 2 in the cell and reading 2 kg/m3.

In text only format you can wrap text, or use Ctrl Enter to add a line in a text only cell.

Is there a way to wrap the text in a custom cell where the description is too long for the cell width?

i.e Cell input is 2 but cell reads 2 weeks before installation window. (Formatted as 0"weeks before installation window"

Of course I could widen the column or use a smaller font but that would be too easy!

 

[joerd]

Not with a custom format. You'll either have to use two cells (too easy !) or use something like [tt]=TEXT(2,"0")&" weeks before installation window"[/tt]

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[zappedagain]

Excel doesn't seem to do very well with automated wrapping and cell heights. You are correct that <Format><Cells><Alignment><wrap text> will automatically wrap the large value for you, but it is very inconsistent about increasing the row height to fit the full value. So you'll get

2
kg/m3

but you may or may not be able to see both lines! With top alignment you might only see "2" and with bottom alignment you might only see "kg/m3".

One trick I found for this is to set the format vertical alignment to 'center'. This way if you do wrap and have two lines and Excel doesn't increase the cell height you'll suddenly see the bottom half of the top line and the top half of the bottom line. This is very UGLY but at least it alerts the viewer that they aren't seeing the correct data until they manually reformat the height for that row. This saved me once when I found that the important part of the data was hidden on the second line and only the first line was visible because 'general' vertical alignment only showed the top line.

Also, a trick I use for data like this is set the cell formula to:

=CONCATENATE(A1, " kg/m3")

Now your text will show up no matter what the value of A1 is (not just 2).