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.