Turn 1 into “1st,” 22 into “22nd,” 13 into “13th.” The English ordinal rule has a tricky teens exception — one formula handles it all.
The example
Numbers with their ordinal suffixes.
| A | B | |
|---|---|---|
| 1 | Number | Ordinal |
| 2 | 1 | 1st |
| 3 | 22 | 22nd |
| 4 | 13 | 13th |
The formula
Number plus the right suffix:
How it works
Last digit decides, except the teens:
- The suffix normally depends on the last digit: 1→st, 2→nd, 3→rd, everything else→th.
- But 11, 12, 13 (and 111, 112…) are exceptions — they take “th,” so test
MOD(A2,100)for 11–13 first. CHOOSE(MIN(MOD(A2,10)+1, 5), "th","st","nd","rd","th")maps the last digit to its suffix.- Concatenate the number and suffix with
&.
For dates, you don’t need this — a custom format or TEXT handles day names. The ordinal formula is for ranks, places, and “Nth” labels in text.
Try it: interactive demo
Enter a number.
Variations
Just the suffix
Drop the number:
Spelled out
1 -> First (use CHOOSE/lookup).
Day with ordinal
e.g. June 1st:
Pitfalls & errors
The teens exception. Forgetting 11–13 gives “11st, 12nd, 13rd.” Always test MOD(n,100) for 11–13 first.
Result is text. “1st” can’t be used in math — keep the number in its own cell.
Negatives/zero. The rule assumes positive counts; guard if other values are possible.
Practice workbook
Frequently asked questions
How do I add ordinal suffixes (st, nd, rd) in Excel?
Why do 11, 12, 13 need special handling?
Can I use ordinals in calculations?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class