I am a supporter of St. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation to St. Joseph's, please.

Information on
St. Joseph's

 

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

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)