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
