Format of Numbers Q

[John]

Is there any way to Format numbers like this

1st
2nd
3rd
4th

etc, etc, etc


Thanks

 

[Bob Phillips]

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)

 

[Gord Dibben]

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