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.

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