ThreeWave Ordinal Suffixes In Excel

This page describes how to add an ordinal suffix to a number.
ShortFadeBar

Introduction

An ordinal suffix is the suffix appended to a number to indicate its position in the series of integers. For example, the text 5th is the number 5 plus the ordinal suffix th. You can apply ordinal suffixes either by formula or code. It is important to note that a number with a ordinal suffix ceases to be a number and becomes text, and therefore you cannot use it in numerical calculations. If you must use these in calculations, you can get the number without the suffix with the formula: 

    =LEFT(E1,LEN(E1)-2)

Formula

The following formula appends the ordinal suffix to the value in cell A1: 

    =A1&IF(AND(MOD(ABS(A1),100)>=10,MOD(ABS(A1),100)<=14),"th",
        CHOOSE(MOD(ABS(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

Note that while the formula appears on two lines here, it is really a single line formula. The formula works with any integer, including negative integers. In the formula above, if A1 contains 23, the result is the text 23rd.

VBA Code

You can also use a VBA function to get the ordinal suffix. The following VBA function returns the suffix that can then be appended to a number to get an ordinal number. 

    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

The result is just the ordinal suffix, and does not include the original number in the result. You can append the suffix to your number, as shown below. 

    Dim N As Long
    Dim Suffix As String
    Dim Result As String
    N = 123
    Suffix = OrdinalSuffix(N)
    Result = Format(N, "#,##0") & Suffix
    Debug.Print Result

ShortFadeBar
LastUpdate This page last updated: 16-Sept-2012.
-->