Format cells to display ordinal numbers ex 21st

[Guest]

I would like to format numbers so that they appear as ordinals

ex
1 1st
2 2nd
3 3rd
4 4th
etc.

 

[Biff]

You need to use a helper cell with a formula or a VBA procedure.

Here's a formula:

A1 = 301

=A1&IF(OR(MOD(A1,100)={11,12,13}),"th",LOOKUP(--RIGHT(A1),{0,"th";1,"st";2,"nd";3,"rd";4,"th"}))

Returns: 301st

Biff