G Guest Oct 17, 2006 #1 I would like to format numbers so that they appear as ordinals ex 1 1st 2 2nd 3 3rd 4 4th etc.
B Biff Oct 17, 2006 #2 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
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
