Was working on a Foxco data project recently where i needed to take dates that were ranked, add a value and then add the proper suffix to them as a number-word (i.e. the 1/1/2025 is the 1st day of the year) and 1/9/2025 is the 9th (not the 9st) day of the year.

=M2&IF(OR(MOD(M2,100)=11,MOD(M2,100)=12,MOD(M2,100)=13),"th",IF(MOD(M2,10)=1,"st",IF(MOD(M2,10)=2,"nd",IF(MOD(M2,10)=3,"rd","th"))))

How this formula works, in simple terms (detailed explanation at the bottom).

In my case, M2 was the cell reference contains the number I wanted to convert.

The formula handles the special cases for 11th, 12th, and 13th (which use "th" instead of "st", "nd", "rd")

For all other numbers, it looks at the last digit to determine the suffix.

Examples:

1 → 1st
2 → 2nd
3 → 3rd
4 → 4th
11 → 11th (not 11st)
21 → 21st
22 → 22nd
23 → 23rd
101 → 101st
111 → 111th (not 111st)

Here's an alternative version that uses A1 and different but equally sound logic.

=A1&IF(AND(MOD(A1,100)>=11,MOD(A1,100)<=13),"th",CHOOSE(MIN(MOD(A1,10)+1,5),"th","st","nd","rd","th"))

Excel Ordinal Formula Breakdown (Nested IF Version)

The Formula:

excel=M2&IF(OR(MOD(M2,100)=11,MOD(M2,100)=12,MOD(M2,100)=13),"th",IF(MOD(M2,10)=1,"st",IF(MOD(M2,10)=2,"nd",IF(MOD(M2,10)=3,"rd","th"))))

Step-by-Step Explanation:

1. M2&

• Takes the number in cell M2 and concatenates it with the suffix
• Example: If M2 = 22, this contributes "22"

2. MOD(M2,100)

• Gets the remainder when dividing by 100 (the last two digits)
• Examples:
  • MOD(1511,100) = 11
  • MOD(22,100) = 22
  • MOD(5,100) = 5

3. OR(MOD(M2,100)=11, MOD(M2,100)=12, MOD(M2,100)=13)

• Checks if the last two digits equal 11 OR 12 OR 13
• This handles special cases: 11th, 12th, 13th, 111th, 112th, 113th, etc.
• Returns TRUE if any condition is met, FALSE otherwise

4. First IF Statement:

excelIF(OR condition, "th", [nested IFs])

• If TRUE: Number ends in 11, 12, or 13 → return "th"
• If FALSE: Continue to the nested IF statements

5. MOD(M2,10)

• Gets the last digit of the number
• Examples:
  • MOD(21,10) = 1
  • MOD(32,10) = 2
  • MOD(43,10) = 3

6. The Nested IF Chain:

excelIF(MOD(M2,10)=1,"st",
  IF(MOD(M2,10)=2,"nd",
    IF(MOD(M2,10)=3,"rd","th")))

This creates a decision tree:

• If last digit = 1 → return "st"
• Else if last digit = 2 → return "nd"
• Else if last digit = 3 → return "rd"
• Else (last digit = 0,4,5,6,7,8,9) → return "th"

Visual Decision Flow:

Start with number in M2
        ↓
Does it end in 11, 12, or 13?
        ↓
    YES → "th"
        ↓
    NO → What's the last digit?
        ↓
    1 → "st"
    2 → "nd"  
    3 → "rd"
    0,4,5,6,7,8,9 → "th"

Examples:

NumberLast 2 digitsSpecial case?Last digitSuffixResult11No1st1st22No2nd2nd33No3rd3rd44No4th4th1111Yes-th11th1212Yes-th12th1313Yes-th13th2121No1st21st2222No2nd22nd2323No3rd23rd11111Yes-th111th

Key Differences from CHOOSE Version:

• Uses nested IF statements instead of CHOOSE function
• Uses OR to check multiple conditions instead of AND with range
• More explicit but longer - each condition is spelled out individually
• Easier to read for beginners but more verbose

Both formulas accomplish the same result, but the first version is more straightforward in its logic flow.