A recent update to Excel included ordinals as an option when you drag with the fill handle. A few months back I made a post about a Custom function for ordinals in dates. Dates are a bit easier because they max out at 31. I thought I would extend the function so that it handled all numbers.
In the image below column A has the ordinals created using the fill handle. Column B has the numbers. And column C is where we will build a formula to create the ordinal based on the number in column B.
Column D checks that the ordinal created in column C matches the ordinal in column A.
The formula that creates the ordinal can be seen in cell C1 in the image below.
The formula uses the num variable to capture the last digit of the number. We multiply it by 1 to convert it back to a number. The RIGHT function always returns a text value, multiplying a text number by 1 converts it into a real number.
The next line where we have B1& will combine the number from cell B1 with the ordinal that follows.
The IF function uses the SUM function to identify if the last two digits in the number equals 11,12 or 13. If they do then we use the th ordinal.
The CHOOSE function which is the FALSE part of the IF function selects the correct ordinal based on the last digit. If the last digit is zero then we will use the number 4 which will return the th ordinal from the CHOOSE function. For the other numbers the MIN function will return either 1, 2, 3 or 4. This provides the correct ordinal for all the other numbers.
Custom function
If you are new to custom functions using the LAMBDA function then please review the blog post below.
The LAMBDA version of the formula in cell C1 for testing is shown in the image below.
The cell reference B1 (in the brackets on the end) is passed to the variable ref which is then passed into the LET function to replace the cell reference.
The range name fnORDINAL is the custom function – see image below.
You can see the custom function is used in cell E1 in the image below. it has been copied down.
Here is another LAMBDA (quite compact) to calculate the ordinal of a given number…
=LAMBDA(num,num&MID(“thstndrdth”,MIN(9,2*RIGHT(num)*(MOD(num-11,100)>2)+1),2))
Recent? You could fill ordinals at least as long ago as Excel 2007.