Text
Format numbers as ordinals: 1st, 2nd, 3rd, 4th (including 11th–13th)
Append accurate English ordinal suffixes ("st", "nd", "rd", "th") to ranks and day numbers while handling the 11th/12th/13th teen exception.
Handle both last-digit rules and the 11–13 teen exception
Most positive integers take their suffix from MOD(n, 10): 1 -> "st" (21st), 2 -> "nd" (22nd), 3 -> "rd" (23rd), and 0 or 4–9 -> "th" (10th, 24th).
Numbers ending in 11, 12, or 13 (11th, 12th, 13th, 112th) are the sole English exception: test AND(MOD(n, 100)>=11, MOD(n, 100)<=13) first to return "th" before checking the ones digit with CHOOSE.
| Number (A) | MOD 100 | MOD 10 | Formatted Ordinal |
|---|---|---|---|
| 1 | 1 | 1 | 1st |
| 12 | 12 (teen) | 2 | 12th |
| 22 | 22 | 2 | 22nd |
| 30 | 30 | 0 | 30th |
Universal positive-integer ordinal formula
IF(MOD(A2, 10)=0, 4, 0) maps multiples of 10 (10, 20, 30) onto slot 4 ("th") inside CHOOSE.
=A2 & IF(AND(MOD(A2, 100)>=11, MOD(A2, 100)<=13), "th", CHOOSE(MIN(4, MOD(A2, 10) + IF(MOD(A2, 10)=0, 4, 0)), "st", "nd", "rd", "th"))Combine directly with RANK.EQ
Bind n = RANK.EQ(...) inside LET to display "1st", "2nd", "3rd" on leaderboards.
=LET(n, A2, n & IF(AND(MOD(n, 100)>=11, MOD(n, 100)<=13), "th", CHOOSE(MIN(4, MOD(n, 10) + IF(MOD(n, 10)=0, 4, 0)), "st", "nd", "rd", "th")))Inputs and interpretation
Use positive integer ranks and English suffixes. The concatenated result is text; retain a numeric rank column for sorting and arithmetic. Other languages require their own ordinal rules.
Try the example
Use the original values or change them to test your understanding.