Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ordinal formating? (Office 2003 SR2)

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.

  3. #3
    3 Star Lounger
    Join Date
    May 2001
    Location
    Berkeley, California, USA
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ordinal formating? (Office 2003 SR2)

    thanks

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    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
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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