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

**Rick Rothstein** · 03-20-2012, 03:46 AM

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.