formatting numbers as 1st 2nd
chiacheng.teg wrote:
On Friday, February 13, 2009 at 5:10:01 AM UTC+8, Bob Arnett wrote:
Is there any way to display numbers in the mode of:
1st
2nd
3rd
4th
etc. ?
Did you not notice that the original post was ***NINE ****ING YEARS AGO***?!
****ing Google Groupies. I wish Google would disable replies after a few days
of no activity.
Or a primitive but simple solution that works:
=day&vlookup(day,vlookup_table,2,false)
A solution that doesn't require manual entry of every number in existence:
=A1&IF(OR(AND(A13,A1<21),A1=0),"th",VLOOKUP(MOD(A 1,10),vlookup_table,2))
....which only requires a 5-line lookup table:
1 st
2 nd
3 rd
4 th
* th
....or, you know, the shorter and much better solution provided by Rick
Rothstein 16 minutes after the original post -- again, ***NINE YEARS AGO***:
=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)2)+1),2)
--
You think you have won. We shall see about that.
|