# 33 Miners

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

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

Posted in Uncategorized

## 12 thoughts on “33 Miners”

1. Rob van Gelder says:

Yes, it was very spectacular rescue. The movie wont do the event justice!

Here’s a dirtier version:

Function ist(lng As Long) As String
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 :)

2. John Walkenbach says:

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”))

3. Rick Rothstein (MVP - Excel) says:

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…

Function Ordinal(Number As Long) As String
Ordinal = Number & Mid\$(“thstndrdthththththth”, 1 – 2 * ((Number) Mod 10) * (Abs((Number) Mod 100 – 12) > 1), 2)
End Function
=A1&MID(“thstndrdth”,MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)
4. Rob says:

Now internationalise it. ;-)

5. Martin says:

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.

6. Terry says:

To make the Spanish glove work on the American localization ‘glove’ you need to use commas.

=A3&MID(“morodorotototomovono”,MIN(9,2*RIGHT(A3)*(MOD(A3-11,100)>2)+1),2)

This is a very useful topic and discussion. I hope others add more for other international solutions.

Thanks.

7. Rick Rothstein (MVP - Excel) says:

@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).

8. fzz says:

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?

Function f(ByVal n As Double) As String
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

9. Rick Rothstein (MVP - Excel) says:

@fzz – What about numbers over 100 (for example, 111)?

10. Jamie Collins says:

@fzz: Why not `f = IIf(n = 11, "th", "st")? Both clauses will be evaluated in all cases, there's no short circuit.`

11. fzz says:

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)))

Function f(n As Double) As String
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

12. hans schraven says:
Function f(n) As String
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

Posting code? Use <pre> tags for VBA and <code> tags for inline.