Ordinal Numbers In Excel
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", 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 This function does not return the
"formatted" number (e.g., "23rd"). It returns only
the =A1&OrdinalSuffix(A1)
Function OrdinalNumber(ByVal
Num As Long) As String You can call this directly from a worksheet cell, as follows: =OrdinalNumber(A1)
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 USAEnglish language conventions. You may have to change some of the code for other language implementations.
