On Fri, 20 Feb 2009 08:35:33 -0500, Ron Rosenfeld
wrote:

On Fri, 20 Feb 2009 01:50:01 -0800, chieflx
wrote:

I am trying to use number format to present sightings in ordinal sequence. I
need to enter multiple sightings in one cell so I would like to be able to
type 1, 2, 3, etc. and it to appear as 1st, 2nd, 3rd etc.. I am not sure how
to set this up or even if it can be done but any help would be gratefully
received.

Many thanks

Chieflx

In order to change the "number format" to represent ordinal numbers, you would
have to change it "on the fly" so to speak.

An event-triggered VBA macro would allow you to do that.

If you just want to enter the number in one cell, and have an ordinal textual
representation of that value in another cell, then you can use functions.

For example, and this assumes that each value is actually entered as a number,
and is not the result of a formula

Right click on the sheet tab and select View Code.
Paste the code below into the window that opens.
Adjust the Set AOI = value to the range you wish to have affected.

=================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, c As Range
Dim Suffix As String
Dim Num As Double

'set to range to be affected
Set AOI = Range("A:A")

If Not Intersect(AOI, Target) Is Nothing Then
For Each c In Intersect(AOI, Target)
If IsNumeric(c.Value) Then
Num = c.Value
If Num < Int(Num) Then
Exit Sub
End If
Select Case Num Mod 10
Case Is = 1
Suffix = "\s\t"
Case Is = 2
Suffix = "\n\d"
Case Is = 3
Suffix = "\r\d"
Case Else
Suffix = "\t\h"
End Select

Select Case Num Mod 100
Case 11 To 19
Suffix = "\t\h"
End Select

c.NumberFormat = "#,##0" & Suffix
End If
Next c
End If
End Sub
=================================
--ron


Minor Change:

=====================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Suffix As String
Dim c As Range
Dim num As Variant
Dim AOI As Range

Set AOI = Range("A:A") 'area to custom format

If Not Intersect(Target, AOI) Is Nothing Then
For Each c In Intersect(Target, AOI)
num = c.Value

If IsNumeric(num) Then
If num = Int(num) Then
Select Case Abs(num) Mod 10
Case Is = 1
Suffix = "st"
Case Is = 2
Suffix = "nd"
Case Is = 3
Suffix = "rd"
Case Else
Suffix = "th"
End Select
Select Case num Mod 100
Case 11 To 19
Suffix = "th"
End Select
c.NumberFormat = "#,##0" & """" & Suffix & """"
End If
Else
c.NumberFormat = "General"
End If

Next c
End If
End Sub
=========================
--ron