Ask Your Question

CALC - Two-line custom format

asked 2016-11-04 10:46:17 +0200

Gwen5484 gravatar image

updated 2016-11-15 14:17:10 +0200

I use a custom format to display a cell containing an integer this way:

image description

My custom format is set to: "Planck "# ##0,0" °C".

The problem is that when I resize the cell, the displayed text doesn't wrap. "Wrap text automatically" is checked.

image description

Is there a way to make it work the way I want ?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-11-15 16:40:36 +0200

Lupp gravatar image

updated 2016-11-15 16:43:18 +0200

A number formatted in a funny way for display is still a number. The option 'Wrap text automatically' applies to text.
Thus you will have to make text from your number if you insist on getting a result diplayed wrapped to more than one lines. This might be done by
=TEXT(MyExpression;Char(34)&"Planck "&CHAR(34)&"# ##0,0"&CHAR(34)&" °C"&Char(34))
or similar. The formula as given, however, will only work in a "comma-locale" or if the respective cell property is set to such a locale.
The next stept to internationalise the hokum for cells with default locale would be
=TEXT(MyExpression;Char(34)&"Planck "&CHAR(34)&"# ##0"&MID(1/2;2,1)&"0"&CHAR(34)&" °C"&Char(34)).
Of course you cannot simply reference cells containing such text for further evaluation without separating the actually numeric part and applying VALUE to it. This might be done (for an example residing in A4, e.g.) by such an obvious formula as
=VALUE(MID(A4;LEN("Planck ")+1;LEN(A4)-LEN("Planck "&" °C")))
In short: Drop the idea.

edit flag offensive delete link more


I guess I could store the integer value in a hidden cell and use your formula for display purpose only, but that would make the modification of the value harder... So as you said, I'll drop the idea. Thanks anyway !

Gwen5484 gravatar imageGwen5484 ( 2016-11-17 09:23:09 +0200 )edit

"...but that would make the modification of the value harder..."
I would rather emphasise the modifications of the sheets and their formula apparatus. If I didi it using an extra column for display I wouldn't hide a nearby column but move all the helper columns for this purpose and others out of sight/out of print range. It's a matter of safe and easy handling, however. Unfortunately hiding contents via the cell format properties for a complete column still leaves the column width in prints.

Lupp gravatar imageLupp ( 2016-11-17 10:29:23 +0200 )edit

Coding a subroutine ("macro") you would also find a way to create a user defined 'Numbers' format code explicitly containing an LF (CHAR(10)) behind the "Planck". It's just the very simple edit line for 'Numbers' formats in the cell properties dialogue which refuses to accept that character. The formatting process itself would correctly handle it.

Lupp gravatar imageLupp ( 2016-11-17 10:37:30 +0200 )edit

Out of curiosity I created a 'Numbers' format containing a LF by a Sub. This part worked as expected and the new format was correctly saved (under No 112 in my case). The LF was, however, suppressed by the internal formatting procedure if '112' style was assigned to a cell. Styles containing a LF are well interpreted by the TEXT function on the other hand. All these tricky ideas tend to make a mess.

Lupp gravatar imageLupp ( 2016-11-17 11:37:16 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-11-04 10:46:17 +0200

Seen: 180 times

Last updated: Nov 15 '16