VBA Express : Multiple Apps - Add The Correct Suffix To Make Any Number Ordinal

Multiple Apps

Add The Correct Suffix To Make Any Number Ordinal

Ease of Use

Easy

Version tested with

2003

Submitted by:

CreganTur

Description:

This code will evaluate any number and determine the correct suffix needed to make the number ordinal.

Discussion:

Suppose you need a number to appear ordinal (1st, 2nd, 13th, 33rd, 94th, etc), but all of your numbers lack the needed suffix. Instead of writing it in by hand, you can use this simple Function to add the correct suffix to any number to make it ordinal.

Code:

instructions for use

			Public Function numSuffix(num As Long) 
     
     'suffix for numbers with last digit of 1
    If Right(num, 2) = 11 Then 
        numSuffix = num & "th" 
    ElseIf CInt(Right(CStr(num), 1)) = 1 Then 
        numSuffix = num & "st" 
         'suffix for numbers with last digit of 2
    ElseIf Right(num, 2) = 12 Then 
        numSuffix = num & "th" 
    ElseIf CInt(Right(CStr(num), 1)) = 2 Then 
        numSuffix = num & "nd" 
         'suffix for numbers with last digit of 3
    ElseIf Right(num, 2) = 13 Then 
        numSuffix = num & "th" 
    ElseIf CInt(Right(CStr(num), 1)) = 3 Then 
        numSuffix = num & "rd" 
         'suffix for all other numbers
    Else 
        numSuffix = num & "th" 
    End If 
     
End Function

How to use:

Create a new Module
Copy the code above
Paste the code into your new module
Click File
Click Save
When Prompted, create a new name to save your module as

Test the code:

In your Module's Immediate Window Type ?numSuffix(x)
Replace the x with any number
An ordinal version of the number will be printed in the Immediate Window

Sample File:

Number Suffix.zip 12.01KB

Approved by mdmackillop

This entry has been viewed 83 times.