Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I format numbers to ordinal i.e. 1st, 2nd etc
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I format numbers to ordinal i.e. 1st, 2nd etc
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I format numbers to ordinal i.e. 1st, 2nd etc
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I format numbers to ordinal i.e. 1st, 2nd etc
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I format numbers to ordinal i.e. 1st, 2nd etc
Hi,
I assume you used this formula from the website =A1&IF(AND(MOD(A1,100)=10,MOD(A1,100)<=14),"th",C HOOSE(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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I format numbers to ordinal i.e. 1st, 2nd etc
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",C HOOSE(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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I format numbers to ordinal i.e. 1st, 2nd etc
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I format numbers to ordinal i.e. 1st, 2nd etc
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I format numbers to ordinal i.e. 1st, 2nd etc
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 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I format numbers to ordinal i.e. 1st, 2nd etc
On Sat, 21 Feb 2009 00:57:01 -0800, chieflx
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you change normal numbers to ordinal numbers ? | Excel Worksheet Functions | |||
duplicate occurance, assigning an ordinal value | Excel Worksheet Functions | |||
Ordinal Numbers | Excel Worksheet Functions | |||
Format cells to display ordinal numbers ex 21st | Excel Worksheet Functions | |||
display dates with ordinal numbers in excel | Excel Discussion (Misc queries) |