ordinal suffixes on ms excel

Excel Tips: Ordinal Suffixes

Excel does not provide users with a number format for expressing ordinal numbers. However, if you would like to convert a number to an ordinal, for example in ranking positions such as 1st, 2nd 3rd, then it is possible to express this using a formula.

As different numbers uses different suffixes (st, nd, rd, th) then it can become a little complicated as you want all numbers to be expressed using the correct suffix.

The following formula would change the number in cell A1 into a 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"))

What does this formula tell Excel?

  • If the last two digits of the number in cell A1 consist of 11, 12 or 12 then the suffix th should be used.
  • If the first point does not apply to this number then the last digit of the number should be checked. If the last digit is 1 then st shall be used, if it is 2 then nd and if 3 then use rd.
  • If both these points do not apply then the suffix th should be used.

It is worth noting that the result of the formula is a text string and therefore cannot be used in any numerical calculations.

You can learn more highly productive Excel tips and techniques in our range of Microsoft Excel training courses.