# How to add st, nd, rd and th to numbers in Excel

Use "th" for 11 to 13, otherwise pick the suffix from the last digit: =A2&IF(AND(MOD(A2,100)>=11,MOD(A2,100)<=13),"th",CHOOSE(MIN(MOD(A2,10)+1,5),"th","st","nd","rd","th")).

**Excel:** `=A2&IF(AND(MOD(A2,100)>=11,MOD(A2,100)<=13),"th",CHOOSE(MIN(MOD(A2,10)+1,5),"th","st","nd","rd","th"))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Place | Ordinal |
| 1 |  |
| 2 |  |
| 3 |  |
| 4 |  |
| 11 |  |
| 12 |  |
| 13 |  |
| 21 |  |
| 22 |  |
| 101 |  |
| 112 |  |

Formula in B2, filled to B12: `=A2&IF(AND(MOD(A2,100)>=11,MOD(A2,100)<=13),"th",CHOOSE(MIN(MOD(A2,10)+1,5),"th","st","nd","rd","th"))`

Result: 1st; 2nd; 3rd; 4th; 11th; 12th; 13th; 21st; 22nd; 101st; 112th

## How it works

The last digit decides the suffix: `MOD(A2,10)` is 1 for "st", 2 for "nd", 3 for "rd", and anything else is "th". CHOOSE picks from the list (MIN caps the position at 5 so 4 to 9 all land on "th"). The exception is checked first: numbers ending in 11, 12 or 13, like 112, always take "th".

## Other ways

- `=A2&IF(OR(AND(MOD(A2,100)>=11,MOD(A2,100)<=13),MOD(A2,10)=0,MOD(A2,10)>3),"th",CHOOSE(MOD(A2,10),"st","nd","rd"))`: The same rule written the other way round: list every "th" case, and CHOOSE only handles 1, 2 and 3.

## Common mistakes

- Testing only the last digit gives "11st", "12nd" and "13rd". The MOD(A2,100) check fixes that.
- The result is text. Keep the number for sorting and math.

## Questions

**How do I write a date like "March 1st"?**

Apply the formula to the day: `=TEXT(B2,"mmmm ")&DAY(B2)&IF(AND(MOD(DAY(B2),100)>=11,MOD(DAY(B2),100)<=13),"th",CHOOSE(MIN(MOD(DAY(B2),10)+1,5),"th","st","nd","rd","th"))`.

**Does it work in Google Sheets?**

Yes, the same formula works.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/add-ordinal-suffix-to-numbers