Ordinal Suffixes for all dates and numbers; Show 3rd instead of 3

A surprising omission from the extensive number formatting goodies in Visual Basic (VB) is the ability to append the appropriate ordinal suffix to a number. This is most often used when writing dates in prose. For example, you would write 3rd February 2009, not 3 February 2009. This VB / VBA function returns your number with the appropriate suffix attached.

A number that ends in a 1 uses the suffix “st”, a 2 “nd”, a 3 “rd” and everything else “th”. Naturally, this being English, there are one or four exceptions that break the grammatical rule. Numbers ending in 11, 12 and 13 all have the suffix “th” and 0 has no suffix. The function below works correctly for any number I threw at it and you can also supply a string containing a number. This is useful for rendering dates which you could convert to a string using Format$ and then send to this function.

Examples

• FormatOrdinal(1) produces “1st”
• FormatOrdinal(2) produces “2nd”
• FormatOrdinal(3) produces “3rd”
• FormatOrdinal(4) produces “4th”
• FormatOrdinal(11) produces “11th”
• FormatOrdinal(111) produces “111th”
• FormatOrdinal("1111") produces “1111th”
• FormatOrdinal(1111.87) produces “1111.87″
• FormatOrdinal("garbage") produces “garbage”
• FormatOrdinal(Format$(#13 Feb 2009#, "d")) produces “13th”
• FormatOrdinal(324989234842) produces “324989234842nd”

Code

''' <summary>
''' Adds the ability to include the ordinal suffix for numbers, ie.,st, nd, rd, th
''' <summary>
Public Function FormatOrdinal( _
                Number) _
                As String

  ' Set a default return value
  FormatOrdinal = (Number)

  ' Only add suffix if a whole numeric value was supplied
  If IsNumeric(Number) Then

    ' Make sure the variant Number is now of a numeric data-type so we can perform
    ' numerical comparisons
    Number = Val(Number)

    If (Number = Int(Number)) And (Number <> 0) Then
      ' Use the last two digits of the number (between 0 and 99) for determining
      ' the suffix. We only use the whole part of the number (Int) and we we use
      ' the Abs function to make sure it is in the range 0 to 99. It is converted
      ' to a string (Format), the right two characters pulled off (Right$) and
      ' converted back to a number (Val).
      Dim Remainder As Long
      Remainder = Val(Right$(Format$(Int(Abs(Number))), 2))

      ' 2 character suffixes for numbers ending in 1 to 9 respectively
      Const Suffixes = "st" & _
                       "nd" & _
                       "rd" & _
                       "th" & _
                       "th" & _
                       "th" & _
                       "th" & _
                       "th" & _
                       "th"

      ' Suffix is "th" if remainder is between 10 and 19 or if it is exactly
      ' divisible by 10
      If ((Remainder >= 10) And (Remainder <= 19)) _
      Or ((Remainder Mod 10) = 0) Then
        FormatOrdinal = Format$(Number) & "th"
      Else
        ' Pull suffix from constant Suffixes using the last digit doubled ((Remainder Mod 10) * 2)
        ' as a starting point
        FormatOrdinal = Format$(Number) & Mid$(Suffixes, ((Remainder Mod 10) * 2) - 1, 2)
      End If
    End If ' Number = Int(Number)
  End If ' IsNumeric(Number)

  End Function

This code was written in Visual Basic for Applications (VB, VBA) for Microsoft Access 2002 (XP) on Windows Vista. It is based on code originally published by Chip Pearson and found via Google and experts-exchange.com. However, this code works for all numbers (not just dates, ie., 1-31) and is much more robust.