This Page: www.cpearson.com/excel/Ordinal.aspx

Last Updated: 06-Nov-2013

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 27-Jan-2014

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Ordinal Suffixes In Excel

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

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)

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

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