Excel

Your community for how-to discussions and sharing best practices on Microsoft Excel. If you’re looking for technical support, please visit Microsoft Support Community.

Forum Discussion

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 FunctionsLike0Reply

6 Replies

Replies sorted byNewest

PremsaharSCopper ContributorFeb 20, 2025First 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”))))Like0Reply
- godschoiceCopper Contributorto PremsaharSMar 25, 2025Thank you. yours was the only version that worked.Like0Reply
Patrick2788Silver ContributorSep 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)) Like1Reply
mtarlerSilver ContributorSep 13, 2023looks 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)2023-09-13 MT formulas for ordinals.xlsx20 KBLike1Reply
- Steve KBrass Contributorto mtarlerSep 14, 2023Thanks 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-309784ed0043Like0Reply
  - mtarlerSilver Contributorto Steve KSep 14, 2023very true; easy solution to wrap the variable with ABS(). see correction abovethat 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.Like1Reply