11 thoughts on “Ordinal Numbers Suffixes”

1. I found a quite interesting way of doing this, though I don’t recall where I got it from. Using one simple formula without any conditional formatting, one can achieve a similar result:

   Assuming value resides in cell A1,

   =A1&MID(“thstndrdth”,MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)

2. Or define the rules as

   If the units digit is 0 or 4-9 or the tens digit is 1, use ‘th’, ELSE ‘st’ for units digit 1, ‘nd’ for units digit 2, and ‘rd’ for units digit 3.

3. fzz –

   Which fails for eleven (11st) twelve (12nd) and thirteen (13rd) :roll:

   …mrt

4. @Michael, did you test it other than in your mind?

   With 1 in A1, =A1+1 in A2 and A2 filled down to A24, B1 containing the formula

   =IF(OR(MOD(A1,10)={0;4;5;6;7;8;9},INT(MOD(A1,100)/10)=1),”th”,CHOOSE(MOD(A1,10),”st”,”nd”,”rd”))

   and B1 filled down to B24, I get the following results.

   1st
   2nd
   3rd
   4th
   5th
   6th
   7th
   8th
   9th
   10th
   11th
   12th
   13th
   14th
   15th
   16th
   17th
   18th
   19th
   20th
   21st
   22nd
   23rd
   24th

5. fzz –

   Hmm. That would be/must be “Guilty as charged, Your Honor.” I was reading your input and looking at the preceding formula. They don’t go together. Now, the people ask, why is it I can read an Excel formula better than I can read a declarative sentence?

   Nice formulation. Thanks and apologies.

   …mrt…mrt…mrt

6. @Muntz,

   I was happy to have been a party to the development of that formula. In case you (or anyone else for that matter) are interested in the genesis for that formula, here is the original thread where it came into existence at….

   http://groups.google.com/group/microsoft.public.excel.misc/browse_frm/thread/d3e93dbaaf5c1064?hl=en&q=biff+ordinal+group:*excel*+author:rothstein

   Look at messages #2 through #11 (you will have to click in the list on the left to see the 11th message). By the way, message #2 contains a one-liner UDF implementing this functionality in case anyone needs it in that format.

7. CAUTION….

   Be careful with the link I just posted… I notice that this blog’s comment processor did not turn all the entire text into a hyperlink, so you will do better by copy/pasting it into your browser.

8. FORMAT QUARTERS TOO!

   Thanks for this… This technique could also be used to format dates in the case of quarters(eg 2001Q1, 2002Q2,…)… Something I was looking for and never found a better solution for!

   Here’s how to achieve it, assuming your date is in D6

   conditional format rule 1:
   =OR(MONTH(D6)=1;MONTH(D6)=2;MONTH(D6)=3)
   custom format to use
   yyyy “Q1?

   conditional format rule 2:
   =OR(MONTH(D6)=4;MONTH(D6)=5;MONTH(D6)=6)
   custom format to use
   yyyy “Q2?

   conditional format rule 3:
   =OR(MONTH(D6)=7;MONTH(D6)=8;MONTH(D6)=9)
   custom format to use
   yyyy “Q3?

   conditional format rule 4:
   =OR(MONTH(D6)=10;MONTH(D6)=11;MONTH(D6)=12)
   custom format to use
   yyyy “Q4?

   Up to now I had to use a macro that was performing a similar test on each cell of the selected range before applying the corresponding formating (eg a different format for each cell). This solution is more elegant.

9. For david’s qtr output:

   year(cell)&”Q”&ceiling(month(cell)/3,1)

   1/31/2012 2012Q1
   2/29/2012 2012Q1
   3/31/2012 2012Q1
   4/30/2012 2012Q2
   5/31/2012 2012Q2
   6/30/2012 2012Q2
   7/31/2012 2012Q3
   8/31/2012 2012Q3
   9/30/2012 2012Q3
   10/31/2012 2012Q4
   11/30/2012 2012Q4
   12/31/2012 2012Q4

10. Pingback: Formatting dates with st, nd, rd etc?

11. I created a simple way of doing this by using IF and MOD try it for the result.

    =IF(AND(MOD(B2,100)>10,MOD(B2,100)<14),B2&"th",IF(MOD(B2,10)=1,B2&"st",IF(MOD(B2,10)=2,B2&"nd",IF(MOD(B2,10)=3,B2&"rd",IF(AND(MOD(B2,100)10),B2&”th”,B2&”th”)))))

    1st
    2nd
    3rd
    4th
    5th
    6th
    7th
    8th
    9th
    10th
    11th
    12th
    13th
    14th
    15th…….goes till 100th, 111th, 121st……..Nth

Posting code? Use <pre> tags for VBA and <code> tags for inline.