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",
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.