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.