Bug with Text function?

[Guest]

Or just an undocumented non-feature;
According to MSFT:
TEXT(value,format_text)
Where:
Format_text is a number format in text form from in the Category box on
the Number tab in the Format Cells dialog box.

So: If Cell A1 has the number 2, in cell B1 I put a formula
=Text(A1,"[red]0.00;[blue]0.00")
where
[red]0.00;[blue]0.00 works just fine as a number format from the category
box of the Number tab in the Format Cells dialog box.

Thougths appreciated.

 

[JE McGimpsey]

Undocumented non-feature.

Functions return values to their calling cells - they can't change cell
format.

TEXT() returns a text string to the cell according to the specified
format, rather than actually changing the format.

Since the format isn't changed, neither is the displayed result.

Instead of using TEXT(), use Conditional Formatting (see Help) instead.

 

[Guest]

The function takes two parameters, a value/ref and a format;
This is right out of the help system:
Format_text is a number format in text form from in the Category box on
the Number tab in the Format Cells dialog box.
In other words, the program isn't wrong, the help file is?
Thanks for the clarification

Undocumented non-feature.

Functions return values to their calling cells - they can't change cell
format.

TEXT() returns a text string to the cell according to the specified
format, rather than actually changing the format.

Since the format isn't changed, neither is the displayed result.

Instead of using TEXT(), use Conditional Formatting (see Help) instead.

Or just an undocumented non-feature;
According to MSFT:
TEXT(value,format_text)
Where:
Format_text is a number format in text form from in the Category box on
the Number tab in the Format Cells dialog box.

So: If Cell A1 has the number 2, in cell B1 I put a formula
=Text(A1,"[red]0.00;[blue]0.00")
where
[red]0.00;[blue]0.00 works just fine as a number format from the category
box of the Number tab in the Format Cells dialog box.

Thougths appreciated.

 

[JE McGimpsey]

No, but it's perhaps not intuitive, and perhaps a confusion with the
usage of "format" in "number format"...

The Help file isn't wrong, in that you can certainly include a format
containing a custom color, like "[Blue]0.00;[Red]0.00" - i.e., it won't
throw an error. So the Help file is correct.

But, like any other function, TEXT() returns a value to the cell, in
this case a text string. By definition, text strings carry no
font/color/etc. format information. Values are displayed according to
the cell format. So the text string will be formatted in the way the
cell's text format is specified.