Office Integration ProjectsNET ProgrammingXML Development

Search The Site:

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)

Formula

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

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.

Created By Chip Pearson at Pearson Software Consulting

This Page: www.cpearson.com/excel/Ordinal.aspx
Last Updated: 06-Nov-2013
Copyright 1997 - 2014, Charles H. Pearson Site Last Updated: 07-May-2016

Email: chip@cpearson.com
Please read this page before emailing me.
Phone: (816) 214-6957 USA Central Time (-6:00 UTC) Between 9:00 AM and 7:00 PM

Essential Tools For Developers

The world's choice for creating NET-based Commercial Quality Add-Ins for Office Add-In Express Is The Most Important Tool For Creating Commerical Level Components