Microsoft Office Tutorials and References
In Depth Information
Expressing a Number as an Ordinal
Expressing a Number as an Ordinal
You might need to express a value as an ordinal number — for example, Today is the 21st day of
the month. In this case, the number 21 is converted to an ordinal number by appending the
characters st to the number. Excel doesn’t have a number format to do this, but you can convert a
number to an ordinal by using a formula.
The specific characters appended to a number (st, nd, rd, or th) depend on the number. The
pattern is a little convoluted, making the construction of a formula tricky. Most numbers use the th
suffix. Exceptions occur for numbers that end with 1, 2, or 3 — except if the preceding number is
a 1 (numbers that end with 11, 12, or 13). These rules might seem fairly complex, but you can
translate them into an Excel formula.
The following formula converts the number in cell A1 (assumed to be an integer) to an ordinal
number:
=A1&IF(OR(VALUE(RIGHT(A1,2))={11,12,13}),”th”,IF(OR(VALUE(RIGHT(A1))={1,2,3}),
CHOOSE(RIGHT(A1),”st”,”nd”,”rd”),”th”))
This formula is rather complicated, so it might help to examine its components. Basically, the
formula works this way:
1. If the last two digits of the number consist of 11, 12, or 13, use th.
2. If Rule 1 doesn’t apply, check the last digit. If the last digit is 1, use st. If the last digit is 2,
use nd. If the last digit is 3, use rd.
3. If neither Rule 1 nor Rule 2 applies, use th.
Figure 104-1 shows the formula in use.

Search JabSto ::

Custom Search