Ordinal Number Format in Excel

[shyam131]

Does any one know how to create a number format in Excel for Ordianl numbers. For example: 1st, 2nd, 3rd....25th. So that if i have a list of numbers I can change them into an ordinal format?

 

[macropod]

Excel doesn’t provide an ordinal formatting style. To overcome this limitation, either of the following formulae can be used to express a value in A1 in an ordinal format:

=A1&IF(AND(MOD(A1,10)=1,MOD(A1,100)<>11),A1&"st",IF(AND(MOD(A1,10)=2,MOD(A1,100)<>12),A1&"nd",IF(AND(MOD(A1,10)=3,MOD(A1,100)<>13),A1&"rd",(A1&"th"))))

=A1&CHOOSE((A1<>{11,12,13})*MIN(4,MOD(A1,10))+1,"th","st","nd","rd","th")

Similarly, the following formula expresses a date in A1 in the form of "Saturday, 1st January 2000":
=TEXT(A1,"dddd, ")&DAY(A1)&CHOOSE((DAY(A1)<>{11,12,13})*MIN(4,MOD(DAY(A1),10))+1,"th","st","nd","rd","th")&TEXT(A1," MMMM ")&YEAR(A1)

 

[GlennUK]

Sorry Macropod, but I don't see how your (A1<>{11,12,13}) can work. This should be replaced with AND(A1<>11,A1<>12,A1<>13) to get the formula to behave properly for 12th and 13th results.

The last 2 formulae would then read:
=A1&CHOOSE(AND(A1<>11,A1<>12,A1<>13)*MIN(4,MOD(A1,10))+1,"th","st","nd","rd","th")

and:
=TEXT(A1,"dddd, ")&DAY(A1)&CHOOSE(AND(DAY(A1)<>11,DAY(A1)<>12,DAY(A1)<>13)*MIN(4,MOD(DAY(A1),10))+1,"th","st","nd","rd","th")&TEXT(A1," MMMM YYYY")

Cheers, Glenn.

 

[macropod]

Yes, that's right - I thought I could use an array constant with this but that doesn't work in this case.

 

[AnotherHiggins]

While there isn't a format that will accomodate you, you *can* simply type in "1st" in A1 then auto-fill down. Excel understands this and will automatically put "2nd," "3rd," "4th" etc. in subsequent rows. the only problem will be if you have to add two of these together, since they are text strings.

 

[bluedragon2]

This will work too:

=IF(AND(VALUE(RIGHT(A1,1))>0,VALUE(RIGHT(A1,1))<4),CHOOSE(VALUE(RIGHT(A1,1)),A1&"st",A1&"nd",A1&"rd"),A1&"th")



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

opps, posted the wrong one, this is the shorter version:

=A1&CHOOSE(VALUE(RIGHT(A1,1)),"st","nd","rd","th")

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Still not thinking:

=A1&CHOOSE(VALUE(RIGHT(A1,1)),"st","nd","rd","th")

needs a condition for ending in 0:

=IF(VALUE(RIGHT(A1,1)=0,A1&"th",=A1&CHOOSE(VALUE(RIGHT(A1,1)),"st","nd","rd","th"))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Forget my posts, I was trying to reinvent the wheel from the above posts and forgot about 11,12,13......

BEEN A LONG DAY



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[GoDawgs]

You'd still need the check for 11, 12 and 13...like the others gave.

 

[GoDawgs]

Sorry, posted that at the same time...

 

[mbarron]

I got this monster to work:

=IF(ISNA(MATCH(RIGHT(A1,2),{"10","11","12","13"},0)),IF(RIGHT(A1,1)="1",A1&"st",IF(RIGHT(A1,1)="2",A1&"nd",IF(RIGHT(A1,1)="3",A1&"rd",A1&"th"))),IF(MATCH(RIGHT(A1,2),{"10","11","12","13"},0),A1&"th",FALSE))

Mike

 

[mbarron]

I had a liitle too much on my formula. It should be this:

=IF(ISNA(MATCH(RIGHT(A1,2),{"10","11","12","13"},0)),IF(RIGHT(A1,1)="1",A1&"st",IF(RIGHT(A1,1)="2",A1&"nd",IF(RIGHT(A1,1)="3",A1&"rd",A1&"th"))),A1&"th")

Mike