This is a great formula, Rick, the best I've seen on the net.

I wanted to add a trick here that I was able to implement using your formula and additional help from another thread. It is about formatting the ordinal suffixes as superscript. This is not a simple matter since there is no way (to my knowledge) to format characters as superscript or subscript within Excel formulas. I got the idea from this thread:

https://ca.answers.yahoo.com/questio...7154456AAU0Dem

The best answer there lists the unicode numbers for the various letters one needs for the ordinal suffixes. These a

Char Hex Decimal
d 1D48 7496
h 02B0 688
n 207F 8319
r 02B3 691
s 02E2 738
t 1D57 7511

Essentially, we need a lookup table that has the four ordinal suffixes in column 1 and the corresponding superscripted versions in column 2. Your formula can then be modified by adding a lookup of the unformatted suffixes and converting them into superscripted ones.

For example, row 1 in the lookup table would have "st" on the left and "=UNICHAR(738)&UNICHAR(7511)" on the right (Excel needs the decimal values). Once we build this table of 4 rows and 2 columns, we should see the superscripted equivalents of column 1 strings in column 2.

Supposing that the number is in A1, the formula with the ordinal suffix in B1 and the lookup table in D1:E4, the formula in B1 should read as follows:

=A1&VLOOKUP(MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MO D(A1-11,100)2)+1),2),D1:E4,2,FALSE)

On Thursday, February 12, 2009 at 4:26:04 PM UTC-5, Rick Rothstein wrote:
Try this formula...

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

--
Rick (MVP - Excel)