Our expert trainers can help you get the best results.
If you would like any further information please contact one of our training advisors.
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.
Payments processed by
