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 c...
excel
Formulas and Functions
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