# Thread: ordinal formating? (Office 2003 SR2)

1. ## ordinal formating? (Office 2003 SR2)

Is it possible to format cells so that they say "1st", "2nd", "3rd" instead of "1", "2", "3'.

2. ## Re: ordinal formating? (Office 2003 SR2)

Excel doesn't have a custom format for ordinals, but you can create a formula that returns an ordinal. Say that A1 contains a number. The following formula will return the number followed by "st", "nd", "rd" or "th":

=A1&IF(OR(MOD(A1,100)=11,MOD(A1,100)=12,MOD(A1,100 )=13),"th",IF(MOD(A1,10)=1,"st",IF(MOD(A1,10)=2,"n d",IF(MOD(A1,10)=3,"rd","th"))))

This is a text value, so you cannot use it in calculations. You should use the original value (in A1 in this example) for calculations.

thanks

4. ## Re: ordinal formating? (Office 2003 SR2)

<P ID="edit" class=small>(Edited by macropod on 10-Apr-06 14:45. Bug fixes, and code expanded to cater for re-calcs.)</P>Another way, which allows you to get the ordinal number formatting without resorting to a formula that converts the number to text is to use a custom number format. The challenge here is to get Excel to apply the appropriate format for the number, since you can't define "st", "nd", "rd" and "th" in the one custom format. The following code modules (which you'd place in the relevant Worksheet object) gets around that, by applying a custom number format that's applicable to the cell's contents:

Private Sub Worksheet_Calculate()
Call Ordinals(ActiveSheet.Range("A1:J10"))
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Call Ordinals(Target)
End Sub

Private Sub Ordinals(ByVal Target As Range)
Dim oCell As Range
If Intersect(Target, ActiveSheet.Range("A1:J10")) Is Nothing Then Exit Sub
On Error Resume Next
For Each oCell In Target
Select Case Abs(Int(oCell.Value)) Mod 10
Case 1
If Abs(Int(oCell.Value)) Mod 100 = 11 Then
oCell.NumberFormat = "0""th"""
Else
oCell.NumberFormat = "0""st"""
End If
Case 2
If Abs(Int(oCell.Value)) Mod 100 = 12 Then
oCell.NumberFormat = "0""th"""
Else
oCell.NumberFormat = "0""nd"""
End If
Case 3
If Abs(Int(oCell.Value)) Mod 100 = 13 Then
oCell.NumberFormat = "0""th"""
Else
oCell.NumberFormat = "0""rd"""
End If
Case Else
oCell.NumberFormat = "0""th"""
End Select
Next
End Sub

As coded, the custom number formats will be applied to any numeric cells in the range A1:J10. You can change that to suit any other range, including a named range. Note that cells containing text are left alone, so you don't have to exclude them from the defined range.

Cheers

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•