how do I format numbers to ordinal i.e. 1st, 2nd etc

[chieflx]

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

 

[Mike H]

Hi,

Look here

http://www.cpearson.com/excel/ordinal.htm

Mike

 

[chieflx]

Sorry I should have mentioned I am using excel 2007

 

[chieflx]

Hi Mike,

Thanks for the quick reply, I have tried copying the formula shown on the
link but it comes up with a 'circular error', unfortunately I do not know
enough about functions and logic equations to solve this. I have tried
changing the A1 to the cell I am using but it still gives the error message.
If you have an idea of how I need to edit the formula I would be grateful.
also do you know if this would allow multiple entries in one cell?

Many thanks
Chieflx

 

[Mike H]

Hi,

I assume you used this formula from the website

=A1&IF(AND(MOD(A1,100)>=10,MOD(A1,100)<=14),"th",CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

If so it can't go in A1. The formula loks at A1 and in another cell returns
the ordinal value of what it finds there.

Mike

 

[chieflx]

Hi,

Thanks for the explanation, I kind of understand it but I'm not sure it
would be suitable for what I am trying to do. I have done a number of
observations on a group of animals and recorded the behaviours, what I am
trying to do is call the 1st animal I see as 1st, the second as 2nd and so
forth. so for example animal 1 shows feeding, animal 2 shows feeding and
movement. in order to show which animal I am referring to I need to enter
1st, 2nd etc. so I can see the range of movements associated with each animal.
I suspect what I am trying to achieve is not possible and may have to stick
with 1,2,3, etc.

Many thanks for your help

chieflx

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[chieflx]

Hi Ron,

Thanks for the reply, it is bit beyond my knowledge of functions but I am
going to work through it to try and understand what the function does but it
may take me some time.

Cheers
Chieflx

 

[Ron Rosenfeld]

Hi Ron,

Thanks for the reply, it is bit beyond my knowledge of functions but I am
going to work through it to try and understand what the function does but it
may take me some time.

Cheers
Chieflx

The routine basically looks at the last digit (or two if it is in the "teens")
and figures out what the ordinal suffix should be. It then uses that to
construct an appropriate number format for the cell.
--ron