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

Hi,

Look here

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

Mike

"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

Sorry I should have mentioned I am using excel 2007

"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

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" wrote:

> Hi,
>
> Look here
>
> http://www.cpearson.com/excel/ordinal.htm
>
> Mike
>
> "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

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" wrote:

> 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" wrote:
>
> > Hi,
> >
> > Look here
> >
> > http://www.cpearson.com/excel/ordinal.htm
> >
> > Mike
> >
> > "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

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

"Mike H" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > Hi,
> > >
> > > Look here
> > >
> > > http://www.cpearson.com/excel/ordinal.htm
> > >
> > > Mike
> > >
> > > "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

On Fri, 20 Feb 2009 01:50:01 -0800, chieflx <(E-Mail Removed)>
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

On Fri, 20 Feb 2009 08:35:33 -0500, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>On Fri, 20 Feb 2009 01:50:01 -0800, chieflx <(E-Mail Removed)>
>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

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" wrote:

> On Fri, 20 Feb 2009 08:35:33 -0500, Ron Rosenfeld <(E-Mail Removed)>
> wrote:
>
> >On Fri, 20 Feb 2009 01:50:01 -0800, chieflx <(E-Mail Removed)>
> >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
>

On Sat, 21 Feb 2009 00:57:01 -0800, chieflx <(E-Mail Removed)>
wrote:

>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