Formatting Numbers as Ordinal (1st, 2nd, 3rd, 4th, etc) (Excel 2010 Training)

There are two ways to accomplish this.

Worksheet Formula

The following formula will return the number in A1 with the suffix appended: 

=A1&MID(“thstndrdth”,MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)

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.

Leave a Reply

Your email address will not be published. Required fields are marked *