Custom Number Format for text

[Cortez]

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

 

[Claus Busch]

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

 

[Cortez]

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

 

[joeu2004]

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))

 

[Ron Rosenfeld]

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.

 

[Ron Rosenfeld]

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.

 

[joeu2004]

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

Who said anything about font color?

 

[Ron Rosenfeld]

Who said anything about font color?

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:

 

[joeu2004]

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

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.

 

[joeu2004]

Even if "this" had referred to CF in general

I should have written: ``Even if "this" had referred to displaying a blank
cell if the cell value is text "null"``.


----- original message -----

 

[Ron Rosenfeld]

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.

I was not trying to be a "smart-ass" but I apologize since it apparently came across that way. The reason I raised the question about using CF was because an hour earlier, someone had posted using the change of the font color to make the cell appear blank in CF, and when you posted subsequently that it could not be done in XL2003, I wondered if my memory was faulty about that feature in XL2003, or if there were some other issue.

Since your posts are generally well thought out, complete and quite detailed, I wondered what I was missing. It never occurred to me that you were unaware of that technique.

 

[joeu2004]

Even if "this" had referred to CF in general, I simply overlooked an
arguably clever use of it.

[....]
The reason I raised the question about using CF was
because an hour earlier, someone had posted using the
change of the font color to make the cell appear blank in CF [....]
It never occurred to me that you were unaware of that technique.

I am not "unaware" of it. I said I [might have] simply "overlooked" it
(i.e. forgot about it).

But actually, looking back at the discussion to refresh my memory, I see
that:

1. Claus wrote: ``For text "null" use the conditional format: if cell value
is text "null" then font color is white``.

2. And Cortez responded: "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".

So why would I reiterate a method that Cortez already rejected?
(Rhetorical.) If I had, I'm sure you would have bleated, "Why are you
repeating a suggestion that Cortez already rejected?".

Instead, I addressed my comments to his original request for a "custom
[number] format" per se. I wanted to make the point that such features
might be version-specific, and if Cortez uses XL2007 and later, he/she
should look at CF for some format enhancements as there are for numeric CFs
(IIRC). But I also set expectations that I did not expect any for text
formats per se.

I posted my comments 3 hours before you offered essentially the same answer
in fewer words.

That's about all I will say on this matter. It is a distraction. I try to
focus my attention and comments on issues that I think will benefit the OP,
occassionally nitpicking others' comments again only when I think it will
benefit the OP.

 

[Ron Rosenfeld]

Instead, I addressed my comments to his original request for a "custom
[number] format" per se.

Hence my misunderstanding when you wrote "CF" and were likely thinking custom number formatting. Again, I apologize for having offended you.

That's about all I will say on this matter. It is a distraction. I try to
focus my attention and comments on issues that I think will benefit the OP,

I agree.

 

[joeu2004]

I apologize for having offended you.

Okay. I'm having a "bad hair" day ;-).