By the way, you might find this one-liner Ordinal function I developed
That's an amazing formula to me. That kind of formula is so eloquent
(says a
lot with a little) and escapes my abilities thus far.
I'm not so sure about the eloquent part, but thank you for the nice words.
Let's see if we can short-circuit your search for why my function works. At
its most basic level, the one-liner breaks down to this...
Ordinal = 0 + Cell.Value & Mid$("thstndrdthththththth", X, 2)
where X is what I'll call "the 'ugly' looking expression" from now on. Since
concatenation has a lower precedence than addition, zero will be added to
the value from the cell first (this forces a blank cell to zero, which is
what your function does) and then the results of the Mid function call will
be concatenated onto that. The Mid function call is straight forward
enough... whatever value X takes on is used to find the starting location
for a substring within that supplied String constant and the 2, of course,
says the returned substring should be two characters long. Notice that the
String constant is made up of the ordinal suffixes for the numbers zero
through nine lumped together in order.
Okay, so how does the expression X works. First, lets isolate the expression
so we can examine it...
1 - 2 * ((Cell.Value) Mod 10) * (Abs((Cell.Value) Mod 100 - 12) > 1)
Because the ordinal suffixes are each two characters long, we need a
mathematical expression that returns 1, 3, 5, 7, etc. (the starting position
in the String constant) for the numerical values 0, 1, 2, 3, etc. (the last
digit in the number from the cell). That mathematical expression is this...
Position = 1 + 2 * Digit
If Digit equals 0, Position equates to 1; if Digit equals 1, Position
equates to 3; if Digit equals 2, Position equates to 5, etc.
Okay, so we can see where the 1 and the 2 come from in our ugly expression,
but why the minus sign and not a plus sign? Well, one of the terms being
multiplied in our ugly expression is a logical expression (returns True or
False) and, in VB/VBA, True values equate to -1 (False values to 0)... in
order to get our plus sign when the logical expression is True, we need a
minus sign to multiply the returned -1 (True) value by in order to make it a
plus value.
So, that explains the 1, the minus sign and the 2... let's now look at the
two terms the 2 is being muliplied by. First, is this....
((Cell.Value) Mod 10)
You alread know what this does because you used it in your own function. For
those reading this response from the archive, the above Mod operator divides
the Cell.Value by 10 and returns the remainder form that division. That
means everything is stripped off of the Cell.Value except for its last
digit. Notice those remainders are 0, 1, 2, 3, etc. These are the same
values I discused a couple of paragraphs ago when we talked about finding
the starting position of the substring. (That number multiplied by 2 and
added to 1 gives the starting position for the substring in the String
constant of suffixes.) This last digit is the correct positional value for
17 of the first 20 numbers... the problem three numbers being 11, 12 and 13.
These numbers do not take the same ordinal suffixes as do 1, 2 and 3, so
when the Cell.Value's last two digits are 11, 12 or 13, we need to modify
the number we multiply the 2 by in order to get the correct suffix. Also
note that within every block of 100 number numbers, those ending with 11, 12
and 13 are the only ones where the suffix "breaks the rules".
Okay, this brings us to the second expression which the 2 is being
multiplied by. This expression is the logical expression we talked about
earlier...
(Abs((Cell.Value) Mod 100 - 12) > 1)
In the same way Mod 10 returned the last digit from the Cell.Value, Mod 100
returns the last 2 digits. Whatever value that is, we subtract 12 from it
and then take the Absolute value of that result (the Abs function simply
removes minus signs from negative values). We then check that result for
being greater than 1. Why? Think about it, of the 100 possible last two
digits of a number, only 11, 12 and 13 will be 1 or less if 12 is subtracted
from them... all other last two digits, when 12 is subtracted from them,
will have an absolute difference greater than 1... and in those cases, the
last digit takes a normal ordinal suffix; hence, we test the subtraction
against >1. If the difference is less than or equal to 1, the logical
expression evaluates to 0 (False) so that the product of the numbers with
the 2 is zero... and, thus, our ugly expression evaluates to 1 for last two
digits of 11, 12 and 13. This means these numbers use the same ordinal
suffix as 0 does... which, for them, is the correct suffix for them.
I'll use it instead of mine if you don't mind.
Of course I don't mind... anything I post in a newsgroup is free for the
reader to use.
Can't get my function working yet with the other ideas
I'm still surprised by this. As I said, your originally posted code works
perfectly for me when place in a Standard Module, and when I comment out my
Option Explicit statement<g> (you should really consider using variable
declaration statements and not relying on default values for Objects; such
as the Value property of the Range statement).
Rick
