How to Convert Numbers to Ordinals in Excel
Rankings and positions read better as 1st, 2nd, 3rd than bare numbers — but Excel has no built-in for it, and the tricky part is that 11, 12 and 13 break the pattern. Here's the formula, and a one-click way.
Last updated: August 2026
| Number | Ordinal | |
|---|---|---|
| 1 | → | 1st |
| 22 | → | 22nd |
| 13 | → | 13th |
Method 1 — the ordinal formula
=A2&IF(OR(MOD(A2,100)={11,12,13}),"th",CHOOSE(MIN(MOD(A2,10)+1,4),"th","st","nd","rd"))- Fill down the column.
- Copy and Paste Special > Values to freeze the labels.
It works, but it's a mouthful to type correctly, and it's easy to fumble the 11-13 exception and end up with "11st".
The 1-click way — Tellsheet
Number to Ordinal converts a whole column of numbers to 1st, 2nd, 3rd, 21st, 22nd, 23rd in one click, with the 11th/12th/13th rule handled for you. Only whole, non-negative numbers are converted; the rest pass through. Optionally render the suffix as small raised letters (1ˢᵗ). Previews first and is revertable. Pairs with Add Serial Numbers and Rank.
Frequently asked questions
How do I add st/nd/rd to a number?
The formula above, or Tellsheet's Number to Ordinal in one click.
Why do 11, 12, 13 use 'th'?
They're the English ordinal exception; correct methods handle it, a last-digit lookup doesn't.
Is the result a number?
No — "1st" is text because of the suffix. Keep the number in another column if you need to calculate.
Related Excel guides
Label rankings the right way
Number to Ordinal turns 1, 2, 3 into 1st, 2nd, 3rd — 11th/12th/13th included — right inside Excel.
Get Tellsheet free See pricing