The CNN banner said “21st Miner Rescued.” To be on topic, I wondered how Excel might automate that. You need a simple algorithm that checks the count mod 10 and the count mod 100. If i mod 10 = 1 and i mod 100 <> 11 then the appropriate suffix for i is “st”. Similar analyses work for i mod 10 = 2 and i mod 10 = 3. For all other cases (4, 11, 12, 13, 14, etc) the suffix is “th.” If you take control of the status bar, it looks like this.
Sub Miners()
Dim i As Long
Dim suffix As String
Application.DisplayStatusBar = True
For i = 1 To 33
If i Mod 10 = 1 And i Mod 100 <> 11 Then
suffix = “st “
ElseIf i Mod 10 = 2 And i Mod 100 <> 12 Then
suffix = “nd “
ElseIf i Mod 10 = 3 And i Mod 100 <> 13 Then
suffix = “rd “
Else
suffix = “th “
End If
Application.StatusBar = i & suffix & “miner rescued!”
Wait (0.8)
Next i
End Sub
Dim i As Long
Dim suffix As String
Application.DisplayStatusBar = True
For i = 1 To 33
If i Mod 10 = 1 And i Mod 100 <> 11 Then
suffix = “st “
ElseIf i Mod 10 = 2 And i Mod 100 <> 12 Then
suffix = “nd “
ElseIf i Mod 10 = 3 And i Mod 100 <> 13 Then
suffix = “rd “
Else
suffix = “th “
End If
Application.StatusBar = i & suffix & “miner rescued!”
Wait (0.8)
Next i
End Sub
Sub Wait(t As Single)
Dim sTime As Single
sTime = Timer + t
Do While Timer < sTime
Loop
End Sub
That’s enough on topic. Here’s a loud cheer for the NASA system engineers the spec’d out the capsule, and another one for the Chilean navy that built it.
…mrt
Yes, it was very spectacular rescue. The movie wont do the event justice!
Here’s a dirtier version:
Select Case Right(0 & lng, 2)
Case 11, 12, 13: ist = “th”
Case Else
Select Case Right(lng, 1)
Case 1: ist = “st”
Case 2: ist = “nd”
Case 3: ist = “rd”
Case Else: ist = “th”
End Select
End Select
End Function
Count the implicit conversions :)
Or, use a formula:
=A1&IF(OR(VALUE(RIGHT(A1,2))={11,12,13}),”th”,IF(OR(VALUE(RIGHT(A1))={1,2,3}),CHOOSE(RIGHT(A1),”st”,”nd”,”rd”),”th”))
Something you and your readers may find interesting… here is a one-liner function and a very short formula for generating ordinal suffixes onto numbers that I have posted to the old newsgroups in the past…
Ordinal = Number & Mid$(“thstndrdthththththth”, 1 – 2 * ((Number) Mod 10) * (Abs((Number) Mod 100 – 12) > 1), 2)
End Function
Now internationalise it. ;-)
Well Rob, I’ll pick up the Spanish glove….
=A1&MID(“morodorotototomovono”;MIN(9;2*RIGHT(A1)*(MOD(A1-11;100)>2)+1);2)
the single letter option is easier, it’s just “o” or “a”, depending on the gender (Remember that numbers in Spanish do have a Gender!!”)
Rgds.
To make the Spanish glove work on the American localization ‘glove’ you need to use commas.
This is a very useful topic and discussion. I hope others add more for other international solutions.
Thanks.
@Martin,
I don’t speak Spanish, so I don’t know what the ordinal suffixes are for it, but since you are using the formula I posted as a basis, that text string (“morodorotototomovono”) will not work correctly. My formula is designed to pick the characters out of the text string two-at-a-time, up to a total of ten characters maximum… yours is twenty characters long, so the last half of it will never be selected. In the English version, the first two characters are for zero (0th), the next three for one, two and three (1st, 2nd and 3rd) and the last two characters for everything else up to nineteen (the rest, up to ninety-nine, repeat only the first ten digits worth… then at the one-hundred point, everything repeats over again).
INDEX({“st”,”nd”,”rd”,”th”},MAX(4*OR(n={11,12,13}),MIN(MOD(n-1,10)+1,4)))
This is a CS101 problem. Treat 11, 12 and 13 as special cases using “th”, then calculate n MOD 10 once.
As for VBA, isn’t this a perfect example of when to use Select rather than multiple Ifs?
n = Fix(n)
Select Case n Mod 10
Case 0, 4 To 9
f = “th”
Case 1
f = IIf(n <> 11, “st”, “th”)
Case 2
f = IIf(n <> 12, “nd”, “th”)
Case 3
f = IIf(n <> 13, “rd”, “th”)
End Select
End Function
@fzz – What about numbers over 100 (for example, 111)?
@fzz: Why not
f = IIf(n = 11, "th", "st")? Both clauses will be evaluated in all cases, there's no short circuit.OK good point about n > 100.
=IF(OR(MOD(n,100)={11,12,13}),”th”,INDEX({“st”,”nd”,”rd”,”th”},MIN(MOD(n-1,10)+1,4)))
n = Fix(n)
If Abs((n Mod 100) – 12) <= 1 Then f = “th”: Exit Function
Select Case n Mod 10
Case 1
f = “st”
Case 2
f = “nd”
Case 3
f = “rd”
Case Else
f = “th”
End Select
End Function
f = Switch(InStr(“123”, n Mod 10) > 0 And (n Mod 100) 10 <> 1, Choose(n Mod 10, “st”, “nd”, “rd”), n > 0, “th”)
End Function