|
An ordinal number is a number that
expresses position in a series, such as 1st, 2nd, or 3rd. Excel does
not have a built in method for appending the suffix to a number.
However, you can very easily write a function, either worksheet formula or a VBA function, to create the suffix.
Worksheet Formula
The following formula will return the number in A1
with the suffix appended:
=A1&IF(AND(MOD(A1,100)>=10,MOD(A1,100)<=14),"th",
CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
For example, if A1 contained 23, the formula
will return 23rd.
VBA Function
The following VBA function will return the suffix
(only):
Function OrdinalSuffix(ByVal
Num As Long) As String
Dim N As Long
Const cSfx = "stndrdthththththth" ' 2 char suffixes
N = Num Mod 100
If ((Abs(N) >= 10) And (Abs(N) <= 19)) _
Or ((Abs(N) Mod 10) = 0) Then
OrdinalSuffix = "th"
Else
OrdinalSuffix = Mid(cSfx, _
((Abs(N) Mod 10) * 2) - 1, 2)
End If
End Function
This function does not return the
"formatted" number (e.g., "23rd"). It returns only
the
appropriate suffix (e.g., "rd").
You can call this directly from a worksheet cell, as follows:
=A1&OrdinalSuffix(A1)
To return the ordinal number, use:
Function OrdinalNumber(ByVal
Num As Long) As String
Dim N As Long
Const cSfx = "stndrdthththththth" ' 2 char suffixes
N = Num Mod 100
If ((Abs(N) >= 10) And (Abs(N) <= 19)) _
Or ((Abs(N) Mod 10) = 0) Then
OrdinalNumber= Format(Num) & "th"
Else
OrdinalNumber= Format(Num) & Mid(cSfx, _
((Abs(N) Mod 10) * 2) - 1, 2)
End If
End Function
You can call this directly from a worksheet cell, as follows:
=OrdinalNumber(A1)
Or you can call it from other VBA procedures, as
follows:
Msgbox "Result Is: "
& 5 & OrdinalSuffix(5)
Both examples above properly handle the case of the
"teenth" numbers, all of which take a "th" suffix,
unlike their other counterparts in other "decades" (e.g., 13th
and 23rd).
Of course, both examples are written for USA-English
language conventions. You may have to change some of the code for other
language implementations.
|
|