Format cells to display ordinal numbers ex 21st

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Prince of Tama - 17 Oct 2006 07:49 GMT

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

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

Reply to this Message

Biff - 17 Oct 2006 08:09 GMT

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

>I would like to format numbers so that they appear as ordinals
>
[quoted text clipped - 4 lines]
> 4 4th
> etc.

Reply to this Message