davids

Copy this UDF to a general module in your workbook.

Function OrdinalNumber(ByVal Num As Long) As String
'You can call this directly from a worksheet cell, as follows:
'=OrdinalNumber(A1)
Dim n As Long
Const cSfx = "stndrdthththththth" ' 2 char suffixes
n = Num Mod 100
If ((Abs(n) = 10) And (Abs(n) <= 19)) _
Or ((Abs(n) Mod 10) = 0) Then
OrdinalNumber = Format(Num) & "th"
Else
OrdinalNumber = Format(Num) & Mid(cSfx, _
((Abs(n) Mod 10) * 2) - 1, 2)
End If
End Function


Gord Dibben MS Excel MVP

On Thu, 16 Mar 2006 15:51:27 -0800, davids
wrote:

Is there a function I could use that would change a column of 1,2,3,4 etc
into a column of 1st,2nd,3rd,4th etc?