You can 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.
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
Many Ordinal RANK() options in Excel with joint, equal rankings, words and more
Excel rankings with ordinal numbers, joint, equal and more
