Ordinal Numbers in Excel, 1st, 2nd, 3rd etc.


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Here’s how to show ordinal numbers in Excel; 1st, 2nd, 3rd.  Sadly, it’s not as simple as you’d think.

ordinal numbers in excel 1st 2nd 3rd etc microsoft excel 16426 - Ordinal Numbers in Excel, 1st, 2nd, 3rd etc.

The ‘Ordinal Rank’ column, 1st, 2nd etc you’d expect to be provided by Microsoft, but it’s not.

There’s no in-built function nor a number format.  After all, Excel has many different number formats already, so ordinal numbers would seem really obvious to have – but no.

You have to resort to either a convoluted CHOOSE function or bit of VBA code.  We’ll show you both.

CHOOSE() for Ordinal numbers

The non-macro way is to use CHOOSE() which uses the first parameter (the ranking) and returns the parameter in the second position for 1, third position for 2 etc.

=CHOOSE([Rank],"1st","2nd","3rd","4th","5th")

ordinal numbers in excel 1st 2nd 3rd etc microsoft excel 16427 - Ordinal Numbers in Excel, 1st, 2nd, 3rd etc.

As you can see, Choose() is limited by the number of positions you create.  We’ve only put in parameters for values 1 to 5 so ranks 6 and higher get an error.

Of course, you can add more parameters but that can get unwieldy.  A class of 20 or 30 students needs a really long Choose() statement.

Ordinal Rank custom function

The alternative is a bit of VBA to make a custom function.

This is a lot easier to understand and will cover all integers.

The OrdinalRank() custom function is borrowed from many Internet sources.  It’s the basis for some related custom functions we’ll be demonstrating in future articles.

The downside is that you’ll have to save the worksheet as an .xlsm worksheet which can scare some people if you share the file.

ordinal numbers in excel 1st 2nd 3rd etc microsoft excel 16429 - Ordinal Numbers in Excel, 1st, 2nd, 3rd etc.

OrdinalRank() at work

It checks the right most digit then adds the correct ending; “st”, “nd” etc.  Any ending digit 4 or more always get “th”.

The second Select Case fixes the special cases for integers ending in 11, 12 and 13.  The code in text is below.

Add the function to a module for the worksheet.

Then use the custom function as usual.

That’s the simple options for ‘proper’ rankings.  It’s doesn’t handle equal rankings and other special cases.  We’ll look at that in a future article.

Function OrdinalRank(pNumber As String) As String
' adapted from various sources for Office-Watch.com

Select Case CLng(VBA.Right(pNumber, 1))

    Case 1
    OrdinalRank = pNumber & "st"

    Case 2
    OrdinalRank = pNumber & "nd"

    Case 3
    OrdinalRank = pNumber & "rd"

    Case Else
    OrdinalRank = pNumber & "th"

End Select

Select Case VBA.CLng(VBA.Right(pNumber, 2))

    Case 11, 12, 13
    OrdinalRank = pNumber & "th"

End Select
End Function

subs profile e1563205311409 - Ordinal Numbers in Excel, 1st, 2nd, 3rd etc.
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address