On Friday, 30 March 2018 at 06:23:27 UTC+1, Auric__ wrote:
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 FUCKING YEARS AGO***?! Fucking 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(A1>3,A1<21),A1=0),"th",VLOOKUP(MOD(A1,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.
Tried the formula above but I have 11st in my data. Lol.
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)