You don't really need the IF test...
See, you learn something new everyday!
Look at that... the "new guy" on the block had something new to show one of
I didn't know you could include multiple format styles in the TEXT
function.
Interesting... I just looked and see that this feature is not mentioned in
the help files for the TEXT function. I find that odd. Anyway, as I have
mentioned before, I am returning to Excel after a very lengthy absence
(10-15 years), so I find I am relearning a lot and coming across lots of new
things; but more importantly, I am not constrained by what everyone else
"knows to be fact". In the case of the TEXT function, it appeared to me to
be a spreadsheet function equivalent to the compiled VB (where I spent most
of my previous 15 years) and/or VBA Format function. I figured that was
confirmed by the constructions possible in Custom Formatting figuring the
same underlying function calls were underneath it all. So, never looking at
the documentation, I just figured that same syntax could be applied; hence,
my matter-of-fact posting in this thread. A quick experiment, though, shows
differences between the TEXT function and Format function's implementation
of this... in the Format function, do this Format(Value,"#;;z\ero") and
negative numbers print out as expected (they adopt the formatting from the
first category section as if the no alternate category sections were used)
whereas doing this TEXT(Value,"#;;z\ero") prints out nothing for negative
numbers (if a category section is shown in the TEXT function, it is used).
For those who may wonder what that means:
A cells format is divided into 4 categories: positive numbers, negative
numbers, 0, and text.
I am not so sure of that fourth category. In the compiled VB world, the
fourth category for the Format function is returned for a NULL value in the
first argument. This also applies to the VBA world. For example, enter these
two lines into the Immediate window in Excel's VBA IDE...
Rick=NULL
? Format(Rick,"#;#;z\ero;Oh No")
and Oh No will print out. I had trouble getting anything to work in the TEXT
function when I tried to make use of the fourth category section. I wonder
if any of this TEXT function stuff is documented?
I see something new in your signature.... Congratulation!
Rick
