Blog

Formula - How to format numbers with ordinal suffixes -  1st, 2nd, 3rd, 4th, etc

This post describes how to format a number with an ordinal suffix, or ordinal indicator. That is, the suffixes st, nd, rd, th that appears after a number (eg 1st, 2nd, 3rd, 4th, etc).

When working with numbers, there may be a requirement to format numbers with ordinal suffixes. For example, to display 1 as 1st, 2 as 2nd, 3 as 3rd, etc.

Use case scenarios include formatting the day number element of a date or producing "rank numbers" for sets of records.

What's are the suffixes that correspond to each number?

This is mostly common knowledge but for clarity, the suffix we add to an input number is as follows:

  • For numbers that end in 1 - we add st (eg 1st, pronounced first)
  • For numbers that end in 2 - we add nd (eg 22nd, pronounced twenty-second)
  • For numbers that end in 3 - we add nd (eg 43rd, pronounced forty-third)
  • For all other numbers - we add th (eg 5th, pronounced fifth)

The exception to this, which could seem strange for non-native English speakers, is that the above rules do not apply to numbers between 11 and 20. The suffix for all of these numbers is th. Therefore, the formula we build must take account of this exception.

The formula to output an ordinal suffix

Taking the above rules into account, here's the syntax to output the correct ordinal suffix, based on the value of a text input control called TextInput1. In practice, we would substitute TextInput1.Text with our target input value.

With({input:
        Left(Text(Value(TextInput1.Text), "00"),2)
      },
     If(Value(input) > 10 And Value(input) < 20, 
        "th",
        Switch(Right(input,1),
               "1", "st", 
               "2", "nd", 
               "3", "rd", 
               "th"
        )
     )
)

To verify the result of this formula, here's a screenshot of the output when we apply the formula to a sequence of numbers.

Formatting the day number in a date with st, nd, rd, th

To demonstrate the most common use case, here's how to format the current date in the in long date format with the correct ordinal suffix for the day number. 

With({inputDate:Now()},
    With({input:
            Left(Text(Text(inputDate, "dd"), "00"),2)
        },
        Text(inputDate, "dd") & 
        If(Value(input) > 10 And Value(input) < 20, 
            "th",
            Switch(Right(input,1),
                "1", "st", 
                "2", "nd", 
                "3", "rd", 
                "th"
            )
        ) & " "  &
         Text(inputDate, "mmmm yyyy")  
    )
)
As the screenshot below highlights how at the time of writing, this produces the result "14th February 2022".


Conclusion

When working with numbers, it may be necessary to format a value with an ordinal suffix (st, nd, rd, th). This post described the formula to carry out this task.
Previous
Next
Related posts