Forum Discussion

Steve K's avatar
Steve K
Brass Contributor
Sep 13, 2023

Convert numbers to ordinal numbers (1st, 2nd, 3rd, etc.)

I suggested to a client they should stop entering ordinals (text values) and use numbers instead to facilitate sorting and calculations. I wish Excel had a number format for this, but it doesn't. I came up with the formula below, but I'm sure there are many ways it could be done. Any thoughts or suggestions? 
 
Explanation: The IF tests whether the value is a number, and if not, it returns "--". If it is a number, then it uses LET to define LstDgt as the last digit of the number, and Lst2Dgts as the last 2 digits. The RIGHT() function returns text, so I used "1*RIGHT()" to force it into a number. Then IFS checks if the last 2 digits are >9 and <14, since those should end in "th". Otherwise it checks the last digit to see if it should be "st", "nd", "rd" or "th".
 
=IF(ISNUMBER(A1#),A1# & LET(LstDgt,1*RIGHT(A1#,1),Lst2Dgts,1*RIGHT(A1#,2),
IFS((Lst2Dgts>9)*(Lst2Dgts<14),"th",
LstDgt=1,"st",
LstDgt=2,"nd",
LstDgt=3,"rd",
TRUE,"th")),"--")
 
or as a defined name called "Ordinal":
=LAMBDA(n,
   IF(ISNUMBER(n),
       n&LET(LstDgt,1*RIGHT(n,1),Lst2Dgts,1*RIGHT(n,2),
        IFS((Lst2Dgts>9)*(Lst2Dgts<14),"th",
               LstDgt=1,"st",
               LstDgt=2,"nd",
               LstDgt=3,"rd",
               TRUE,"th"))
       ,"--"))

 

It's simple to use when the LAMBDA form is used in a defined name:

 


 


 

excel
Formulas and Functions

6 Replies

  • PremsaharS's avatar
    PremsaharS
    Copper Contributor
    Feb 20, 2025

    First Set of formulas doesn't work for me. Im using office 2010. I couldn't try the Lamda.

    so created the below sets. Might be useful for someone. 

    =IF(AND(1*RIGHT(A1, 2)>3,1*RIGHT(A1, 2)<21),A1&"th",IF(1*RIGHT(A1, 1)=1,A1&"st",IF(1*RIGHT(A1, 1)=2,A1&"nd",IF(1*RIGHT(A1, 1)=3,A1&"rd",A1&"th"))))

    • godschoice's avatar
      godschoice
      Copper Contributor
      Mar 25, 2025

      Thank you. yours was the only version that worked.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Sep 13, 2023

    Steve K 
    Playing with a lambda in Python for Excel:

     

    ordinal = lambda n: "%d%s" % (n,"tsnrhtdd"[(n//10%10!=1)*(n%10<4)*n%10::4])
    
for i in range(1,100):
    print(ordinal(i))

     

  • mtarler's avatar
    mtarler
    Silver Contributor
    Sep 13, 2023

    looks good to me. I found a trivial improvement I think:

     

     

    =IF(ISNUMBER(A1#),A1# & LET(
      LstDgt,RIGHT(A1#,1)*(LEFT(RIGHT(TEXT(A1#,"00"),2),1)<>"1"),
      SWITCH(LstDgt,1,"st",2,"nd",3,"rd","th")),
    "--")

     

     

    note edited based on error pointed out to me (ty SnowMan55 )  and alternate solution using some of SnowMan's idea using MOD instead of text is added below:

     

     

     

    =IF(ISNUMBER(pos_int), pos_int & LET(
       LstDgt, MOD(ABS(pos_int), 10) * (QUOTIENT(MOD(ABS(pos_int), 100), 10) <> 1),
       SWITCH(LstDgt, 1, "st", 2, "nd", 3, "rd", "th")),
   "--")

     

     

     

    and note in reply to the PM from SnowMan, that version didn't work on arrays because it used AND( array>=11, array<=13) but the AND function is applied across the whole array and should be re-written as (array>=11)*(array<=13) and then it will work (see attached)

    sorry for shifting to here but the PM system was giving me errors/complaints.  I think it finally went through but here it is so it is included with the OP.  In the attached the GL version is credited to SnowMan55 (Glenn Lubiens)

    • Steve K's avatar
      Steve K
      Brass Contributor
      Sep 14, 2023
      Thanks for these alternatives. The solution using MOD runs into problems for negative numbers. Here's a discussion of it in case you're interested - https://answers.microsoft.com/en-us/msoffice/forum/all/mod-for-negative-numbers/5a16dd87-558a-4067-ab48-309784ed0043
      • mtarler's avatar
        mtarler
        Silver Contributor
        Sep 14, 2023

        very true; easy solution to wrap the variable with ABS(). see correction above

        that said there is not check or correction for decimal numbers.  0.1 or 1.2 or etc...

        those could be 'pre-screened' using 

        =IF(ISNUMBER(pos_int)*(pos_int=INT(pos_int)), ...

        which would return the "--" from the end or could have its own nested IF and then return itself.

Resources