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

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

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")

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.

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

Want More?

Office Watch has the latest news and tips about Microsoft Office.  Delivered once a week.