I'd like to build a custom number format where the text format section
contains an if statement. Here is an example to explain what I'd like
to replicate:

#,##0_);(#,##0);;if="null" then ""

The number format would leave treat positive and negative values
normally, would display a blank cell if the value is zero, and would
display a blank cell if the cell value is text "null", otherwise if
the cell value is any other text it would display that text.

1,-2,0,null,X would become 1,-2,,,X

I'm not sure if what I am asking makes sence, so please let me know if
I can clarify my question.

What I want is an example of a custom number format that would do
this. If even possible.

Thanks in advance,
TK

Hi TK,

Am Fri, 7 Oct 2011 13:09:39 -0700 (PDT) schrieb Cortez:

The number format would leave treat positive and negative values
normally, would display a blank cell if the value is zero, and would
display a blank cell if the cell value is text "null", otherwise if
the cell value is any other text it would display that text.

1,-2,0,null,X would become 1,-2,,,X

try it with custom number format:
#,##0;-#,##0;;@
For text "null" use the conditional format:
if cell value is text "null" then font color is white


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

That's virtually the same as what I have atm, but was looking for
something more eloquent. I was hoping that there was a similar
conditional operater function for the text section as there is for the
numeric sections where [100] dictates when to apply the format.

Thanks for the suggestion!

TK

On Oct 7, 3:32*pm, Claus Busch wrote:
Hi TK,

Am Fri, 7 Oct 2011 13:09:39 -0700 (PDT) schrieb Cortez:

The number format would leave treat positive and negative values
normally, would display a blank cell if the value is zero, and would
display a blank cell if the cell value is text "null", otherwise if
the cell value is any other text it would display that text.

1,-2,0,null,X *would become 1,-2,,,X

try it with custom number format:
#,##0;-#,##0;;@
For text "null" use the conditional format:
if cell value is text "null" then font color is white

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Fri, 7 Oct 2011 13:39:15 -0700 (PDT), Cortez wrote:

I was hoping that there was a similar
conditional operater function for the text section as there is for the
numeric sections where [100] dictates when to apply the format.

Unfortunately, there is not.

"Cortez" wrote:
I'd like to build a custom number format where the text
format section contains an if statement. Here is an
example to explain what I'd like to replicate:
#,##0_);(#,##0);;if="null" then ""

The number format would leave treat positive and negative
values normally, would display a blank cell if the value
is zero, and would display a blank cell if the cell value
is text "null", otherwise if the cell value is any other
text it would display that text.
1,-2,0,null,X would become 1,-2,,,X

You do not mention the version of Excel that you use.

AFAIK, XL2003 does not have any custom formats or format subtypes for Text.
I am pretty sure the same can be said for XL2007 and later.

Also, you cannot use Conditional Formatting to accomplish this in XL2003.
But I believe CF is more robust in XL2010, perhaps XL2007 as well.
Nonetheless, I don't know if it is robust enough to accomplish this kind of
"formatting". I doubt it.

Usually, you must accomplish this in your formula. For example:

=IF(ISNUMBER(A1),A1,IF(A1="null","",A1))

On Fri, 7 Oct 2011 14:40:33 -0700, "joeu2004" wrote:

Also, you cannot use Conditional Formatting to accomplish this in XL2003.

I'm surprised. I thought you could format font color in CF in XL2003.

"Ron Rosenfeld" wrote:
On Fri, 7 Oct 2011 14:40:33 -0700, "joeu2004" wrote:
Also, you cannot use Conditional Formatting to accomplish this in XL2003.

I'm surprised. I thought you could format font color in CF in XL2003.

Who said anything about font color?

On Fri, 7 Oct 2011 19:18:10 -0700, "joeu2004" wrote:

"Ron Rosenfeld" wrote:
On Fri, 7 Oct 2011 14:40:33 -0700, "joeu2004" wrote:
Also, you cannot use Conditional Formatting to accomplish this in XL2003.

I'm surprised. I thought you could format font color in CF in XL2003.

Who said anything about font color?

Perhaps I misunderstood. When you wrote:

Also, you cannot use Conditional Formatting to accomplish this in XL2003.

I thought that by "this" you were referring to the OP's request to:

display a blank cell if the cell value is text "null"

"Ron Rosenfeld" wrote:
On Fri, 7 Oct 2011 19:18:10 -0700, "joeu2004" wrote:
"Ron Rosenfeld" wrote:
On Fri, 7 Oct 2011 14:40:33 -0700, "joeu2004" wrote:
Also, you cannot use Conditional Formatting to accomplish this in
XL2003.

I'm surprised. I thought you could format font color in CF in XL2003.

Who said anything about font color?

Perhaps I misunderstood. When you wrote:

Also, you cannot use Conditional Formatting to accomplish this in XL2003.

I thought that by "this" you were referring to the OP's request to:

display a blank cell if the cell value is text "null"

I think I know where you are going with that. But if you have an "aha!",
why not simply spit it out and show us all how smart you are instead of
being a smart-ass and distorting the comments of others.

Even if "this" had referred to CF in general, I simply overlooked an
arguably clever use of it. Again, you could simply present your idea
instead of casting contentless aspersions on my comments.

FYI, "this" referred to "any custom formats or format subtypes for Text". I
wrote, "But I believe CF is more robust in XL2010, perhaps XL2007 as well",
because I think (IIRC) that someone pointed out elsewhere that XL2010
(and/or XL2007) CF now permits us to specify numeric formats per se
conditionally based on cell values.

I might be wrong about that; I don't have XL2007 or XL2010 to double-check.
Note that I wrote "IIRC". If you have XL2007 and/or XL2010, perhaps you can
comment on that. Ah, "that" refers to the specification of numeric formats
per se in Conditional Formatting.

But as I continued to write, ``Nonetheless, I don't know if it is robust
enough to accomplish this kind of "formatting". I doubt it``. Ah, "it"
refers to the specification of formats per se in Conditional Formatting.
And "this kind of formatting" refers to the OP's request for a "text format"
per se.