Custo Format for TEXT Function

G

gr8posts

According to Excel help pages for the TEXT function it is possible to use
custom format with the TEXT function.
I have tried everything but I can't make it accept colour codes as [Red] etc.
Does any one knows why or how ?

I know I can use Custom from Format Cells menu or Conditional formating to
achive the desired format, but I am interested to make it work with the TEXT
function.

Thank you

PS. I am using Excel 2002 and 2003
 
G

gr8posts

Thank you for the immediate answer
I have spent hours and hours trying to find a solution because I trusted the
Help.
We can conclude then that Help is wrong in this case concerning TEXT
function, since we can’t use any format from Number tab of Format Cells menu.
The strange thing is that it is ONLY the colour codes that are not accepted
all the other symbols #.###,00 etc work just fine. I hope it is not a bug in
excel versions I am using.
Just by cusiosity Do you know if it is still a problem in 2007 ?

Thank you again


Peo Sjoblom said:
No, it is not possible to get those formats using the TEXT function

--
Regards,

Peo Sjoblom


gr8posts said:
According to Excel help pages for the TEXT function it is possible to use
custom format with the TEXT function.
I have tried everything but I can't make it accept colour codes as [Red]
etc.
Does any one knows why or how ?

I know I can use Custom from Format Cells menu or Conditional formating
to
achive the desired format, but I am interested to make it work with the
TEXT
function.

Thank you

PS. I am using Excel 2002 and 2003
Click to expand...
Click to expand...
 
G

Gord Dibben

Could you please copy/paste the help file wording that you are concluding is
incorrect.

I can find nothing in help that states you can custom format text entries to a
color.

I am curious..........maybe I am missing part of my help file.


Gord Dibben MS Excel MVP
 
G

gr8posts

I didn’t say that I found in help that we can use colour for text.
I said that:"we can’t use any format from Number tab of Format Cells menu"

and the help for the TEXT function (copy/paste) writes:
"Format_text is a number format in text form from in the Category box on
the Number tab in the Format Cells dialog box"

And since Custom is one of them(Category in Numbers tab). I was expecting
following custom format to work with TEXT function since it works as custom
format via the Format->Cells->Custom menu.
[Green]#.##0,00;[Red]-#.##0,00;[Yellow]#.##0,00;[Blue]\"@\"
Results of this custom format are:
Green for positive numbers, “-“ and red for negative numbers, yellow for 0
and blue between double quotes for text.
As you can see text is blue with this custom format So Yes it is possible to
get colour for text via custom format. I don't know if this is written in any
help or manual but it works. :)

I hope to be clearer now.
Thank you

Ps I don't know if you should use same characters for " ; , . " I am in
Europe.
 
G

Gord Dibben

Good example of text being colored.

Thanks for the update...........I have learned something here.

Gord

I didn’t say that I found in help that we can use colour for text.
I said that:"we can’t use any format from Number tab of Format Cells menu"

and the help for the TEXT function (copy/paste) writes:
"Format_text is a number format in text form from in the Category box on
the Number tab in the Format Cells dialog box"

And since Custom is one of them(Category in Numbers tab). I was expecting
following custom format to work with TEXT function since it works as custom
format via the Format->Cells->Custom menu.
[Green]#.##0,00;[Red]-#.##0,00;[Yellow]#.##0,00;[Blue]\"@\"
Results of this custom format are:
Green for positive numbers, “-“ and red for negative numbers, yellow for 0
and blue between double quotes for text.
As you can see text is blue with this custom format So Yes it is possible to
get colour for text via custom format. I don't know if this is written in any
help or manual but it works. :)

I hope to be clearer now.
Thank you

Ps I don't know if you should use same characters for " ; , . " I am in
Europe.

Gord Dibben said:
Could you please copy/paste the help file wording that you are concluding is
incorrect.

I can find nothing in help that states you can custom format text entries to a
color.

I am curious..........maybe I am missing part of my help file.


Gord Dibben MS Excel MVP
Click to expand...
Click to expand...
 
Top