Results 1 to 1 of 1

Thread: Ordinal Suffix (i.e., "st", "nd", "rd" and "th")

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13

    Ordinal Suffix (i.e., "st", "nd", "rd" and "th")

    Here is an Excel function that can be used in a cell formula to apply the proper ordinal suffix to the number specified (in cell A1 for this example)...

    MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)

    More than likely, you will use this function within an Excel formula by concatenating it after the number that is specified with in the function. For example, if cell B1 contains the number, then your formula could be something like this...

    ="Ordinal for B1: "&B1&MID("thstndrdth",MIN(9,2*RIGHT(B1)*(MOD(B 1-11,100)>2)+1),2)

    Note: On my display, there appears to be a space between the B and the 1 in the last B1 cell reference... if you see one there too, ignore it as it is a mirage... actually there is no space and if you copy/paste the formula, it will paste correctly into an Excel worksheet.

    Another example might be this which makes use of the TODAY function directly (although more efficient would be to put =TODAY() in a cell and use the repeated cell reference rather than the repeated call to the Volatile TODAY function)...

    ="Today is the "&DAY(TODAY())&MID("thstndrdth",MIN(9,2*RIGHT(DAY( TODAY()))*(MOD(DAY(TODAY())-11,100)>2)+1),2)&" of the month."

    It is believed that this is the most compact Excel function for calculating the ordinal suffix of any valid Excel number (there are shorter ones for shorter specialize ranges of numbers). Credit for the final form of this function must go to a man named Biff who posts under the name T. Valko on various Excel forums who took a very compact formula that I posted and squeezed it down further to the form posted above. For those interested in how this function came about, here is a link to the newsgroup thread that gave birth to it...

    http://groups.google.com/group/micro...othstein&pli=1

    For the programmers out there, if you need this functionality inside a VBA procedure, you can use this function, which also works as a UDF (if you didn't follow the above link, this is the function that eventually gave rise to the above Excel function)...

    Code:
    Function Ordinal(Number As Long) As String
      Ordinal = Number & Mid$("thstndrdthththththth", 1 - 2 * ((Number) Mod 10) * (Abs((Number) Mod 100 - 12) > 1), 2)
    End Function
    This function returns the number with its ordinal suffix concatenated onto it.
    Last edited by Rick Rothstein; 11-27-2013 at 11:28 AM. Reason: Modified the example formulas a little to make them fit the text better.

Similar Threads

  1. VBA Versions of my "Get Field" and "Get Reverse Field" formulas
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 4
    Last Post: 06-02-2017, 06:15 PM
  2. Get "Reversed" Field from Delimited Text String
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 3
    Last Post: 02-22-2015, 09:01 AM
  3. Reversing a "First Middle Last" Name to "Last, First Middle" Name Format
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 5
    Last Post: 01-06-2014, 10:04 PM
  4. Replies: 5
    Last Post: 04-18-2013, 02:30 AM
  5. Follow-up to "Excel Number Format: Indian Style Comma Separation"
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 04-14-2012, 10:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •