Skip to content
Fox Consulting - Foxco
Blog

Menu

Work

  • clients
  • tools
  • reach
  • news

Tech

  • wordpress
  • shopify
  • woocommerce
  • ecommerce
  • hosting
  • shift4shop

Marketing

  • google ads
  • seo aeo
  • amazon
  • fractional cmo
Foxco//Article

    Written by

    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.

    bottom dock header
    Uncategorized

    The Bottom Dock Header and why we love it

    10/21/2026 — andy fox
    Wordpress Broken FixWP
    Business

    Why we Built FixWP (Fix WordPress)

    09/16/2026 — andy fox
    Why do Squarespace Wix Airo & other AI websites look so cheap?
    CSS

    Why do Squarespace Wix Airo & other AI websites look so cheap?

    09/15/2026 — andy fox
    Do Deactivated WordPress Plugins Slow Down My Site?
    Help Tickets

    Do deactivated WordPress plugins slow down my site?

    09/12/2026 — andy fox
    Why hire a microagency
    Data

    Why Hire A Microagency In 2026

    09/01/2026 — andy fox
    Wordpress Admin Email Save
    Plugins

    Change WordPress Admin Email Without Confirming (Plugin)

    07/15/2026 — andy fox
    Your Product Files Suck
    Data

    Brands: Your Product File Sucks

    06/26/2026 — andy fox
    A Box Nerd’s Guide to eCommerce Packaging
    Data

    A Box Nerd’s Guide to eCom Packaging

    05/09/2026 — andy fox
    1 2 3 … 18 →

    WORK

    our genius clients

    Annenberg
    Mishimoto
    Saleen
    Troika
    RainX
    Cheapi
    Batts.biz
    Ustream
    NOCO
    Tiger Battery
    Eastwood
    Continental
    BBOS
    22OR
    Annenberg
    Mishimoto
    Saleen
    Troika
    RainX
    Cheapi
    Batts.biz
    Ustream
    NOCO
    Tiger Battery
    Eastwood
    Continental
    BBOS
    22OR
    Annenberg
    Mishimoto
    Saleen
    Troika
    RainX
    Cheapi
    Batts.biz
    Ustream
    NOCO
    Tiger Battery
    Eastwood
    Continental
    BBOS
    22OR

    TECH

    technologies we love

    React JS
    Ghost CMS developer
    HTML5 PHP
    WooCommerce
    Wordpress
    Shopify
    Python
    Magento
    BigCommerce
    GatsbyJS
    Shift4Shop
    Meta
    Amazon
    Epicor
    Klaviyo
    Drupal
    Zapier
    React JS
    Ghost CMS developer
    HTML5 PHP
    WooCommerce
    Wordpress
    Shopify
    Python
    Magento
    BigCommerce
    GatsbyJS
    Shift4Shop
    Meta
    Amazon
    Epicor
    Klaviyo
    Drupal
    Zapier
    React JS
    Ghost CMS developer
    HTML5 PHP
    WooCommerce
    Wordpress
    Shopify
    Python
    Magento
    BigCommerce
    GatsbyJS
    Shift4Shop
    Meta
    Amazon
    Epicor
    Klaviyo
    Drupal
    Zapier

    Big Agency Depth + Micro Agency Focus.

    Big agencies bring big-brand experience, and small teams bring agility and a sharp focus on your niche -- Foxco is an Arizona micro agency that gives you both. With 500+ companies and 100+ years of combined experience in competitive markets behind us, you work directly with senior people, get decisions made fast, and get the kind of expert, hands-on execution big agencies just aren't built to deliver.

    Ask your doctor if hiring a microagency is right for you

    Foxco - Made in the USA

    Arizona
    Built

    serving businesses coast to coast & beyond

    Foxco is a microagency built around uncommon experience and skills. Foxco is centrally based in Phoenix Arizona and has served 500+ clients across the US and beyond with 100+ combined years of Big Agency level experience paired with unmatched skills, focus and agility.

    500+
    companies served
    100+
    years combined experience
    100%
    US-based team
    0%
    sales pressure
    LET'S CHAT
    Consulting eCom Wordpress Shopify $999 Website Google Amazon Fractional CMO Fix Wordpress
    PhoneEmailFoxco NewsFacebookYoutubeWhatsappPrivacy PolicyAccessibilityWordpress Hosting