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

Microsoft Access logoA 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

”’

”’ Adds the ability to include the ordinal suffix for numbers, ie.,st, nd, rd, th
”’

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[/sourcecode] 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.

2 thoughts on “Ordinal Suffixes for all dates and numbers; Show 3rd instead of 3

  1. I don’ t know how long ago you wrote your code but I wrote, what seems to me to be, a MUCH simpler function to accomplish the same task. Actually, I wrote 2 versions of the function – 1 that allows you to pass in a variant, just like yours, and 1 that only allows you to pass a variable of type Long into it. Below is the version of the function that allows a variant to be passed into it…

    Public Function OrdinalStr(ByVal vNum As Variant) As String
    Dim sTmp As String
    OrdinalStr = (vNum)
    If IsNumeric(vNum) And Val(vNum) = Int(vNum) And Val(vNum) 0 Then
    If vNum Mod 100 > 10 And vNum Mod 100 < 20 Then
    sTmp = vNum & "th"
    Else
    Select Case vNum Mod 10
    Case 1
    sTmp = vNum & "st"
    Case 2
    sTmp = vNum & "nd"
    Case 3
    sTmp = vNum & "rd"
    Case Else
    sTmp = vNum & "th"
    End Select
    End If
    OrdinalStr = sTmp
    End If
    End Function

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s