Ordinal Numbers Suffixes

Starting with Excel 2007, it is possible to specify the number format as part of conditional formatting. This note shows how to leverage that capability to add the appropriate suffix to an ordinal number (e.g., 1st, 2nd, 3rd, 4th, etc.)

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0122%20ordinal%20number%20suffixes.shtml

Tushar Mehta

Posted in Uncategorized

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. 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.

Leave a Reply

Your email address will not be published.