Q: Automatic Ordinals in Excel 2010+

I have to admit that I am a little stunned that Microsoft has never included Ordinal-formatting this as a format option in Excel-x, let alone Excel 2010. It seems so crazy that such a powerful product would not include this. All I can think is that there's some 'good reason' that they have not.

All I want is to have a series of numbers, dates, etc. that I can right-click and select an Ordinal format and have it work; without playing about with special formulae, macros, cunning conditional formatting, fancy scripting,etc.

I have tried some of the solutions offered via searches and the like, but these are without a doubt far too cumbersome for simple application, especially if you need to email spreadsheets to others that are either outside of your macro or script or office version arena, don't know about the specialised Conditional formatting or macros.

I suspect that this is one of those little things that will perhaps just fade into obscurity. Funnily enough, it is catered to in all other Office products in auto-text, just not in Excel?

I look forward to a simple solution, preferably from Microsoft themselves.

A:

Mike,

Can you pl. explain how it works?

Thanks,

Sheeloo

Hi,

I first saw this posted by; I think, Rick Rothstein, and was fascinated by the way it works because it on the one hand looks complicated but when deciphered is brilliantly simple.

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)

We are trying to return MID("thstndrdth",n,2)

Where n is either 1,3,5,7 or 9 to give us th,st,nd etc

The 1,3,5,7 & 9 are determined by the last 2 digits of the number in A1 as follows.

First this bit of the formula 2*RIGHT(A1) multiplies the last digit of A1 by 2  and will return an even number in the range 0 to 18

0,2,4,6,8,10,12,14,16,18

We then take the last 2 digits of the number, even if there aren't 2 it doesn't matter, and apply this formula

MOD(A1-11,100)>2

This to me is the clever part where11 is subtracted from the number and then take MOD 100 of that result and test if the number is greater than 2 returning either TRUE or FALSE

The table below shows the results of those 2 formula for the numbers 1 to 20.

Columns 2 and 3 are the 2 formula discussed above.
Column 4 is the 2 formula multiplied together
Column 5 is the reult +1
Column 6 is the formula MIN(9, result of column 5)

So as you can see the result of

MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1)

Is always 1,3,5,7 or 9 which is used in the MID formula to return the string with the 1,3,5,7,9 being of course determined by the number in A1.

 1 TRUE 2 2 3 3 2 TRUE 4 4 5 5 3 TRUE 6 6 7 7 4 TRUE 8 8 9 9 5 TRUE 10 10 11 9 6 TRUE 12 12 13 9 7 TRUE 14 14 15 9 8 TRUE 16 16 17 9 9 TRUE 18 18 19 9 10 TRUE 0 0 1 1 11 FALSE 2 0 1 1 12 FALSE 4 0 1 1 13 FALSE 6 0 1 1 14 TRUE 8 8 9 9 15 TRUE 10 10 11 9 16 TRUE 12 12 13 9 17 TRUE 14 14 15 9 18 TRUE 16 16 17 9 19 TRUE 18 18 19 9 20 TRUE 0 0 1 1