Number format 1st, 2nd, 3rd etc

[Paul Sheppard]

I am using excel 2000

Is it possible to format numbers to be 1st, 2nd etc

[VBA Noob]

Don't think you can without code which would change the cell value.

Here's some links and a formula.

http://www.cpearson.com/excel/ordinal.htm
http://www.mvps.org/dmcritchie/excel...me.htm#ordinal
http://xldynamic.com/source/xld.RANK.html

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

VBA Noob

[Paul Sheppard]

Don't think you can without code which would change the cell value.

Here's some links and a formula.

http://www.cpearson.com/excel/ordinal.htm
http://www.mvps.org/dmcritchie/excel...me.htm#ordinal
http://xldynamic.com/source/xld.RANK.html



VBA Noob

Thanks for the links

[daddylonglegs]

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

You actually need an AND function in there, otherwise it won't work correctly for 12 and 13, i.e.

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

Also, if your numbers might be as high as 111 you need a MOD function too....so to cover all possible numbers

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

Edit: just so that I can find this thread again if I search ....."ordinal numbers"

[Paul Sheppard]

You actually need an AND function in there, otherwise it won't work correctly for 12 and 13, i.e.

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

Also, if your numbers might be as high as 111 you need a MOD function too....so to cover all possible numbers

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

Edit: just so that I can find this thread again if I search ....."ordinal numbers"

Thanks daddylonglegs