# 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.