English Ordinal Indicators in Excel using VBA


I often need to determine the ordinal indicator (st/nd/rd/th) associated with a number, especially when I am working with dates. In this post, we will learn to write a simple user defined function in VBA, which will identify the ordinal indicator of an integer. Since the logic is quite straightforward, let us see how we can use the different control structures available in VBA.

A quick refresher on English Ordinal Indicators

The general rule is: numbers ending with 1, 2 and 3, get the ordinal indicators ‘st’, ‘nd’ and ‘rd’ respectively. The exceptions to the previous rule are numbers ending with 11, 12 and 13, which get the ‘th’ indicator.
The rest of the numbers get the ‘th’ indicator.

What are control Structures?

For those of you who are new to programming, in general, any code is executed one line after another. And control structures let you break that rule. They let us jump around unconditionally or conditionally. They even let us keep looping through lines. We will be focussing only on the control structures that let us jump around conditionally, which are generally referred to as Decision Structures.

The two popular decision structures available in VBA are:

  1. If… Then… Else
  2. Select… Case

Follow the links in the list for a detailed account on using these control structures. Although these links are related to VB.NET, and not VBA specifically, almost all the principles apply.

I highly recommend reading about the Select Case statement, as it allows you to implement complex decisions with minimal lines of code. I have used some of these principles in this post, and in the end, I will leave you with a weird way of using the Select Case statement, which I prefer over the If… Then… ElseIf… Else… structure.

Now let us take a look at how we can use the control structures to fetch the ordinal indicator of a number. Notice that I am using the Long data type to accept a number as it will let me work even with very large numbers. The integer data type would suffice for most use cases though.

If… Then… Else

Here is the UDF written using the If… Then… Else structure:

Function OrdinalIndicator(ByVal Number As Long) As String
 
If Number Mod 100 = 11 Or _
    Number Mod 100 = 12 Or _
    Number Mod 100 = 13 Then
    OrdinalIndicator = "th"
    ElseIf Number Mod 10 = 1 Then
        OrdinalIndicator = "st"
    ElseIf Number Mod 10 = 2 Then
        OrdinalIndicator = "st"
    ElseIf Number Mod 10 = 3 Then
        OrdinalIndicator = "rd"
    Else
        OrdinalIndicator = "th"
End If

OrdinalIndicator = Number & OrdinalIndicator

End Function

Select… Case

Here is the UDF written using the Select… Case Structure:

Function OrdinalIndicator(ByVal Number As Long) As String

Select Case Number Mod 100
    Case 11 To 13
        OrdinalIndicator = "th"
    Case Else
        Select Case Number Mod 10
            Case 1
                OrdinalIndicator = "st"
            Case 2
                OrdinalIndicator = "nd"
            Case 3
                OrdinalIndicator = "rd"
            Case Else
                OrdinalIndicator = "th"
        End Select
End Select

OrdinalIndicator = Number & OrdinalIndicator

End Function

Now that you have seen the regular implementation of the Select.. Case statement, let us look at the quirky way I promised earlier. You can pass TRUE as the test expression to the Select statement, and then use Boolean expression beside the Case statements to convert the Select… Case control structure into a compact If… Then… ElseIf… Else structure.

Let me show you what I mean:

Function OrdinalIndicator(ByVal Number As Long) As String

Select Case True
    Case Number Mod 100 = 11, Number Mod 100 = 12, _
         Number Mod 100 = 13
        OrdinalIndicator = "th"
    Case Number Mod 10 = 1
        OrdinalIndicator = "st"
    Case Number Mod 10 = 2
        OrdinalIndicator = "nd"
    Case Number Mod 10 = 3
        OrdinalIndicator = "rd"
    Case Else
        OrdinalIndicator = "th"
End Select

OrdinalIndicator = Number & OrdinalIndicator

End Function

Downloads

I hope that you learnt something new about control structures today. Click on the link below to download a workbook, which contains the three UDFs.

Advertisements

Removing non-printing characters from text in Excel using VBA


Have you ever encountered a perfectly constructed MATCH or a VLOOKUP function failing for no apparent reason? Have you had VBA tell you that a file does not exist even though it does? You may have non-printing characters to blame. There are notorious space-like characters that are invisible to the naked eye, which sneak in when you are importing data from Web Pages, Word Documents or PDFs. In this post we will learn more about these characters and how to remove them from your data.

Continue reading “Removing non-printing characters from text in Excel using VBA”

Is the WorksheetFunction property necessary in Excel VBA?


Excel’s Application object’s WorksheetFunction property is a container for Microsoft Excel worksheet function. This property returns a WorksheetFunction object that allows VBA access to the rich set of functions that are built into Excel. While VBA has some generic functions of its own, Excel’s set is much bigger, and more suited for (you guessed it) Excel. As soon as you type in the dot after WorksheetFunction, you will see a list of Excel functions that you can use in your VBA code. But do you really need the WorksheetFunction object?

Continue reading “Is the WorksheetFunction property necessary in Excel VBA?”

Are you Sure? Get user’s consent before running a Macro in Excel VBA for Beginners


I have wasted many hours loading huge amounts of data into spreadsheet models, only to inadvertently click the ‘Reset’ button at the last moment, because it was the day my colleague decided to bump into my chair. Excel is not capable of undoing actions performed by a macro, making it very important to confirm whether a user meant to click a button, before performing certain operations. In this post, we will look at how to get the user’s permission before running a procedure, using the MsgBox function.

Continue reading “Are you Sure? Get user’s consent before running a Macro in Excel VBA for Beginners”

Introduction to Regular Expressions in VBA

Regular Expression

Regular-Expressions (RegExp) is something I bumped into when my string manipulation requirements jumped to an advanced level. Before I started using this powerhouse, all my string manipulation functions involved maneuvering through a dozen for-loops; and tackling hundreds of Left-Right-Mid-InStr functions. RegExp is a pattern matching system that allows you to perform advanced string manipulations very easily. It may take a while to get used to it, but once you get the hang of it, the possibilities are endless.

Continue reading “Introduction to Regular Expressions in VBA”

Reversing a string in Excel

String Reverse in Excel

In my many years of rummaging through the internet for help, I have seen countless posts where troubled help seekers are told that VBA does not have a built in function to reverse a string. Amature know-alls suggest a makeshift solution using a for-loop; and everyone is happy. Contrary to popular belief, Visual Basics does have a built in function to reverse a string: StrReverse()

Continue reading “Reversing a string in Excel”

Concatenate Range and Retain Formatting

Concatenate Range with Formats

In one of my previous posts, I wrote a function to concatenate the values stored in all the cells of a specified range. That function holds good, if you have a text stored in all the cells, and you just want to club them together. It fails if you have cells containing numeric values: dates, percentages, currencies or time. In another post, I wrote a function that returns a formatted string of the value stored in a cell, by automatically fetching the cell’s Number Format.

Continue reading “Concatenate Range and Retain Formatting”

An upgrade to the Text() function in Excel

Struggling to Excel

Many of us are familiar with the Text() function. It comes in handy when you set up excel to draft reports for you. It converts a numeric value stored in a cell to a string based on  the format specified by the user. The only thing I hate about that function is that you have to specify the format every time you use it. Another drawback is, the format of the text is not linked to the cell formatting of the cell that is being referred to. For instance, if you later decide to change the cell formatting of a cell, you also have to change the Special-Format-String argument in the Text() function.

Continue reading “An upgrade to the Text() function in Excel”