“To make pleasures pleasant, shorten them.” [Charles Buxton]

Abstract

Excel has no built-in function for ordinal numbers such as 1st, 2nd, 3rd, 4th, … 100th.

Solutions

=A1&MID("thstndrd",(LEFT(RIGHT("0"&A1,2))<>"1")*(MOD(A1,10)<4)*MOD(A1,10)*2+1,2)
or

=A1&MID("thstndrd"&REPT("th",16)&REPT("thstndrdthththththth",8),2*MOD(A1,100)+1,2)

But Rick Rothstein and Biff (T. Valko) came up with the shortest known formula:

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)