 Ordinal Suffixes In Excel 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
```  This page last updated: 16-Sept-2012.
-->