Format of Numbers Q
 

Is there any way to Format numbers like this

1st
2nd
3rd
4th

etc, etc, etc


Thanks

Not formatting, but take a look at
http://www.xldynamic.com/source/xld.RANK.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" wrote in message
...
Is there any way to Format numbers like this

1st
2nd
3rd
4th

etc, etc, etc


Thanks

How about a UDF?

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 Excel MVP

On Sat, 4 Dec 2004 21:39:21 -0000, "John" wrote:

Is there any way to Format numbers like this

1st
2nd
3rd
4th

etc, etc, etc


Thanks