Formatting numbers to display as "1st, 2nd, 3rd..."

[GVF]

Excel...When using autofill, if two cells contains "1st" and "2nd" and you use autofill, the autofilled cells will display 3rd, 4th...120th...133rd etc. Is there a way to format a number in VBA so the result is the correct "th" or whatever? I am using a select case to do it, but it seems clumsy. Excel tells me that I have entered the numbers as text but won't explain the formatting it is utilizing for autofill. (A msgbox will use the number to display "1st Place" or whatever place the contestant is in.

GVF

 

[taupirho]

Found this on the web


Function OrdinalNumber(ByVal Num As Long) As String

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


You can call this directly from a worksheet cell, as follows:

=OrdinalNumber(A1)

 

[macropod]

Hi

Here's a simpler version of the same function:

Function OrdinalNumber(ByVal Num As Long) As String
Dim N As Long
N = Num Mod 100
If ((Abs(N) > 10) And (Abs(N) < 14)) Or ((Abs(N) Mod 10) > 3) Or ((Abs(N) Mod 10) = 0) Then
    OrdinalNumber = Num & Format(Abs(N), "t\h")
Else
   OrdinalNumber = Num & Format(Abs(N) Mod 10 - 2, "r\d;\st;\n\d")
End If
End Function

Note: You lose the ability to format the number with currency, commas & decimals this way. If you need commas (I doubt you'd need currency & decimals), you could change the two occurrences of

OrdinalNumber = Num &

to

OrdinalNumber = Format(Num, "#,##0;-#,##0;0") &

Cheers

[MS MVP - Word]

 

[GVF]

Macropod wins. His snippet is three lines shorter.

Thanks.

G