Convert Numbers into String with Ordinal Suffix
Posted by
Carlo Linga
Labels:
Visual Basic
The strOrdinal function uses the Mod operator to get the last two digits of the number. For all values between 11 and 19, apply “th” as the suffix. Otherwise, it again uses Mod operator to get the last digit of the number and apply the following rules:
Here’s the code for the function that converts numbers into a string with ordinal suffix:
The VBA code below allows you to represent a numeric value as its ordinal position in a set. The VBA function takes a numeric value and returns the number with the correct ordinal suffix as a string.
For example:
- strOrdinal(1)
- strOrdinal(42)
The strOrdinal function uses the Mod operator to get the last two digits of the number. For all values between 11 and 19, apply “th” as the suffix. Otherwise, it again uses Mod operator to get the last digit of the number and apply the following rules:
- Numbers that end in 1, use “st”
- Numbers that end in 2, use "nd"
- Numbers that end in 3, use "rd"
- Otherwise, use "th"
Here’s the code for the function that converts numbers into a string with ordinal suffix:
Public Function strOrdinal(lngVal As Long) As String
Dim intDigit As Integer
Dim intTeen As Integer
Dim strSuffix As String
' Check for the "teens" first
intTeen = lngVal Mod 100
If intTeen >= 11 And intTeen <= 19 Then
' if teens, use "th"
strSuffix = "th"
Else
' Get the last digit
intDigit = lngVal Mod 10
Select Case intDigit
Case 1
strSuffix = "st"
Case 2
strSuffix = "nd"
Case 3
strSuffix = "rd"
Case Else
strSuffix = "th"
End Select
End If
strOrdinal = lngVal & strSuffix
End Function
Subscribe to:
Post Comments (Atom)
August 15, 2013 at 4:42 AM
Hi, it is fantastic, but what about 11th, 12th and 13th? 13rd or 13th, which one is correct? Thanks your help.
Post a Comment