Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 48
Default formatting numbers as 1st 2nd

Is there any way to display numbers in the mode of:
1st
2nd
3rd
4th
etc. ?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default formatting numbers as 1st 2nd

Try this formula...

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Bob Arnett" wrote in message
...
Is there any way to display numbers in the mode of:
1st
2nd
3rd
4th
etc. ?


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default formatting numbers as 1st 2nd

Hi,

I don't know if this will meet your needs, but type 1st into a cell and then
use the fill handle to drag down as far as necessary.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Bob Arnett" wrote:

Is there any way to display numbers in the mode of:
1st
2nd
3rd
4th
etc. ?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default formatting numbers as 1st 2nd

Rick Rothstein wrote:
Try this formula...

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)2)+1),2)


Nice. Much shorter than what I was thinking:

=A1&IF(AND(MOD(A1,100)10,MOD(A1,100)<14),"th",
LOOKUP(RIGHT(A1,1),{"0","1","2","3","4"},{"th","st ","nd","rd","th"}))
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 83
Default formatting numbers as 1st 2nd

if you have 2007 type all the data 1 2 3 8
and if there is several 1 and 3 and whatever
on the home tab you see find and replace
a window pops us select find 1 and replace with 1st and repeat
easiest way i can think little time consuming though
don't know much about your sheet

"Bob Arnett" wrote:

Is there any way to display numbers in the mode of:
1st
2nd
3rd
4th
etc. ?



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 83
Default formatting numbers as 1st 2nd

wow I have to check that formula out

"Rick Rothstein" wrote:

Try this formula...

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Bob Arnett" wrote in message
...
Is there any way to display numbers in the mode of:
1st
2nd
3rd
4th
etc. ?



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default formatting numbers as 1st 2nd

You might be interested in the genesis for that formula. Click the link
below and read messages 2 thru 11...

http://groups.google.com/group/micro...thor:rothstein

--
Rick (MVP - Excel)


"Glenn" wrote in message
...
Rick Rothstein wrote:
Try this formula...

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)2)+1),2)


Nice. Much shorter than what I was thinking:

=A1&IF(AND(MOD(A1,100)10,MOD(A1,100)<14),"th",
LOOKUP(RIGHT(A1,1),{"0","1","2","3","4"},{"th","st ","nd","rd","th"}))


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default formatting numbers as 1st 2nd

It is believed to be the shortest formula that will add the ordinal suffixes
to a number. As I posted to Glenn, you might be interested in the genesis
for that formula. Click the link below and read messages 2 thru 11...

http://groups.google.com/group/micro...thor:rothstein

--
Rick (MVP - Excel)


" wrote in message
...
wow I have to check that formula out

"Rick Rothstein" wrote:

Try this formula...

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Bob Arnett" wrote in message
...
Is there any way to display numbers in the mode of:
1st
2nd
3rd
4th
etc. ?




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default formatting numbers as 1st 2nd

On Thu, 12 Feb 2009 13:10:01 -0800, Bob Arnett
wrote:

Is there any way to display numbers in the mode of:
1st
2nd
3rd
4th
etc. ?


If you want to be able to refer to these values as NUMBERS and be able to
perform mathematical operations on them, you will need to use a VBA macro to
actually set the format. Otherwise, Rick's formula should work fine.

To use a VBA macro, you could use event-triggered code and within the code
determine the area you wish to format.

To enter this, right-click on the sheet tab and select View Code from the
dropdown menu.

Paste the code below into the window that opens.

As written, it will format any integer entered into column A according to your
requirements. If you need this to work in another area, you only need to
change the set AOI line to the appropriate range.

(If you enter a non-integer into that range, it will reset the format to
General. This may or may not be appropriate for your requirements, and could
be changed easily).

========================================
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
Application.EnableEvents = False
For Each c In Intersect(Target, AOI)
num = c.Value

If IsNumeric(num) And 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 & """"
Else
c.NumberFormat = "General"
End If

Next c
End If
Application.EnableEvents = True
End Sub
====================================
--ron
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default formatting numbers as 1st 2nd

Is there any way to display numbers in the mode of:
1st
2nd
3rd
4th
etc. ?


If you want to be able to refer to these values as NUMBERS and be able to
perform mathematical operations on them, you will need to use a VBA macro
to
actually set the format. Otherwise, Rick's formula should work fine.

To use a VBA macro, you could use event-triggered code and within the code
determine the area you wish to format.

To enter this, right-click on the sheet tab and select View Code from the
dropdown menu.

Paste the code below into the window that opens.

As written, it will format any integer entered into column A according to
your
requirements. If you need this to work in another area, you only need to
change the set AOI line to the appropriate range.

(If you enter a non-integer into that range, it will reset the format to
General. This may or may not be appropriate for your requirements, and
could
be changed easily).

========================================
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
Application.EnableEvents = False
For Each c In Intersect(Target, AOI)
num = c.Value

If IsNumeric(num) And 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 & """"
Else
c.NumberFormat = "General"
End If

Next c
End If
Application.EnableEvents = True
End Sub
====================================


A couple of comments on your event code...

1) My tests show the changing the format of a cell does not kick off a
Change event, so both of your EnableEvents statement lines can be
eliminated.

2) Your "If IsNumeric(num) And num = Int(num) Then" statement will fail if
text is entered into one of the target cells (the Int function call will
fail). You can use this statement instead...

If not num Like "*[!0-9]*" Then

which makes sure that a non-digit is not located anywhere within the
contents of the num variable (it handles both the IsNumeric and "is integer"
issues with one test).

Here is your macro modified as per the above comments...

'====================================
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 Not num Like "*[!0-9]*" 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 & """"
Else
c.NumberFormat = "General"
End If

Next c
End If
End Sub
'====================================

And here is a modification that shortens the routing by eliminating the two
Select Case blocks (but which is just a *tad* more obfuscated<g)...

'====================================
Private Sub Worksheet_Change(ByVal Target As Range)
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 Not num Like "*[!0-9]*" Then
c.NumberFormat = "#,##0""" & Mid$("thstndrdthththththth", _
1 - 2 * (num Mod 10) * (Abs(num Mod _
100 - 12) 1), 2) & """"
Else
c.NumberFormat = "General"
End If
Next
End If
End Sub
'====================================

--
Rick (MVP - Excel)



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default formatting numbers as 1st 2nd

On Fri, 13 Feb 2009 00:12:54 -0500, "Rick Rothstein"
wrote:



A couple of comments on your event code...

1) My tests show the changing the format of a cell does not kick off a
Change event, so both of your EnableEvents statement lines can be
eliminated.


Just habit, but you are correct.


2) Your "If IsNumeric(num) And num = Int(num) Then" statement will fail if
text is entered into one of the target cells (the Int function call will
fail). You can use this statement instead...

If not num Like "*[!0-9]*" Then

which makes sure that a non-digit is not located anywhere within the
contents of the num variable (it handles both the IsNumeric and "is integer"
issues with one test).


I noted that also, and was going to post a correction this morning. Yours is
succinct, but fails on ERROR values with a type mismatch error.




And here is a modification that shortens the routing by eliminating the two
Select Case blocks (but which is just a *tad* more obfuscated<g)...


I find shortened routines to be quite useful sometimes, but I prefer clarity in
this instance.

Here is my corrected routine:

=======================================
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
====================================

and here is another in case some of the entries in the range to be formatted
might be the results of formulas, since target will no longer be within the
area of interest:

======================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Suffix As String
Dim num As Variant
Dim c As Range
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
c.NumberFormat = "#,##0" & """" & Ord(num) & """"
Else
c.NumberFormat = "General"
End If
End If
Next c
End If

On Error Resume Next
For Each c In AOI.SpecialCells(xlCellTypeFormulas, xlNumbers)
num = c.Value
If num = Int(num) Then
c.NumberFormat = "#,##0" & """" & Ord(num) & """"
Else
c.NumberFormat = "General"
End If
Next c
On Error GoTo 0
End Sub

Private Function Ord(num) As String
Select Case Abs(num) Mod 10
Case Is = 1
Ord = "st"
Case Is = 2
Ord = "nd"
Case Is = 3
Ord = "rd"
Case Else
Ord = "th"
End Select
Select Case num Mod 100
Case 11 To 19
Ord = "th"
End Select

End Function
==================================
--ron
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 76
Default formatting numbers as 1st 2nd

On Feb 13, 12:47*pm, Ron Rosenfeld wrote:
On Fri, 13 Feb 2009 00:12:54 -0500, "Rick Rothstein"

wrote:

A couple of comments on your event code...


1) My tests show the changing the format of a cell does not kick off a
Change event, so both of your EnableEvents statement lines can be
eliminated.


Just habit, but you are correct.



2) Your "If IsNumeric(num) And num = Int(num) Then" statement will fail if
text is entered into one of the target cells (the Int function call will
fail). You can use this statement instead...


* * * * *If not num Like "*[!0-9]*" Then


* *which makes sure that a non-digit is not located anywhere within the
contents of the num variable (it handles both the IsNumeric and "is integer"
issues with one test).


I noted that also, and was going to post a correction this morning. *Yours is
succinct, but fails on ERROR values with a type mismatch error.



And here is a modification that shortens the routing by eliminating the two
Select Case blocks (but which is just a *tad* more obfuscated<g)...


I find shortened routines to be quite useful sometimes, but I prefer clarity in
this instance.

Here is my corrected routine:

=======================================
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
====================================

and here is another in case some of the entries in the range to be formatted
might be the results of formulas, since target will no longer be within the
area of interest:

======================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Suffix As String
Dim num As Variant
Dim c As Range
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
* * * * * * c.NumberFormat = "#,##0" & """" & Ord(num) & """"
* * * * Else
* * * * * * c.NumberFormat = "General"
* * * * End If
* * End If
* * Next c
End If

On Error Resume Next
For Each c In AOI.SpecialCells(xlCellTypeFormulas, xlNumbers)
* * * * num = c.Value
* * * * If num = Int(num) Then
* * * * * * c.NumberFormat = "#,##0" & """" & Ord(num) & """"
* * * * Else
* * * * * * c.NumberFormat = "General"
* * * * End If
* * Next c
On Error GoTo 0
End Sub

Private Function Ord(num) As String
* * Select Case Abs(num) Mod 10
* * * * Case Is = 1
* * * * * * Ord = "st"
* * * * Case Is = 2
* * * * * * Ord = "nd"
* * * * Case Is = 3
* * * * * * Ord = "rd"
* * * * Case Else
* * * * * * Ord = "th"
* * End Select
* * Select Case num Mod 100
* * * * Case 11 To 19
* * * * * * Ord = "th"
* * End Select

End Function
==================================
--ron


If you want to work from a date, not just a number then i have put
together a formula...

A1 =
01/01/2009

B1 =
=IF(LEFT(TEXT(A1,"dd"),1)="0",MID(TEXT(A1,"dd"),2, 1),LEFT(TEXT
(A1,"dd"),2))&IF(AND(MOD(LEFT(TEXT(A1,"dd"),2),100 )=10,MOD(LEFT(TEXT
(A1,"dd"),2),100)<=14),"th",CHOOSE(MOD(LEFT(TEXT(A 1,"dd"),2),
10)+1,"th","st","nd","rd","th","th","th","th","th" ,"th"))

Note: This is made for European date format.

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default formatting numbers as 1st 2nd

See my 2 inline comments...

2) Your "If IsNumeric(num) And num = Int(num) Then" statement will fail if
text is entered into one of the target cells (the Int function call will
fail). You can use this statement instead...

If not num Like "*[!0-9]*" Then

which makes sure that a non-digit is not located anywhere within the
contents of the num variable (it handles both the IsNumeric and "is
integer"
issues with one test).


I noted that also, and was going to post a correction this morning. Yours
is
succinct, but fails on ERROR values with a type mismatch error.


There is that damned behind-the-scenes VB type coercion screwing around with
things again. There is a simple fix for my Like operator statement that will
handle errors as well. This If..Then statement should work fine...

If Not CStr(num) Like "*[!0-9]*" Then


And here is a modification that shortens the routing by eliminating the
two
Select Case blocks (but which is just a *tad* more obfuscated<g)...


I find shortened routines to be quite useful sometimes, but I prefer
clarity in
this instance.


Yeah... that is why I made the obfuscation comment and added the <g tag to
it.


--
Rick (MVP - Excel)

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default formatting numbers as 1st 2nd

This is a great formula, Rick, the best I've seen on the net.

I wanted to add a trick here that I was able to implement using your formula and additional help from another thread. It is about formatting the ordinal suffixes as superscript. This is not a simple matter since there is no way (to my knowledge) to format characters as superscript or subscript within Excel formulas. I got the idea from this thread:

https://ca.answers.yahoo.com/questio...7154456AAU0Dem

The best answer there lists the unicode numbers for the various letters one needs for the ordinal suffixes. These a

Char Hex Decimal
d 1D48 7496
h 02B0 688
n 207F 8319
r 02B3 691
s 02E2 738
t 1D57 7511

Essentially, we need a lookup table that has the four ordinal suffixes in column 1 and the corresponding superscripted versions in column 2. Your formula can then be modified by adding a lookup of the unformatted suffixes and converting them into superscripted ones.

For example, row 1 in the lookup table would have "st" on the left and "=UNICHAR(738)&UNICHAR(7511)" on the right (Excel needs the decimal values). Once we build this table of 4 rows and 2 columns, we should see the superscripted equivalents of column 1 strings in column 2.

Supposing that the number is in A1, the formula with the ordinal suffix in B1 and the lookup table in D1:E4, the formula in B1 should read as follows:

=A1&VLOOKUP(MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MO D(A1-11,100)2)+1),2),D1:E4,2,FALSE)

On Thursday, February 12, 2009 at 4:26:04 PM UTC-5, Rick Rothstein wrote:
Try this formula...

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)

  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default formatting numbers as 1st 2nd

On Thursday, February 12, 2009 at 4:26:04 PM UTC-5, Rick Rothstein wrote:
Try this formula...

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)2)+1),2)

--
Rick (MVP - Excel)


"Bob Arnett" wrote in message
...
Is there any way to display numbers in the mode of:
1st
2nd
3rd
4th
etc. ?


Thanks, this was just what I needed!


  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default formatting numbers as 1st 2nd

On Friday, February 13, 2009 at 5:10:01 AM UTC+8, Bob Arnett wrote:
Is there any way to display numbers in the mode of:
1st
2nd
3rd
4th
etc. ?



Or a primitive but simple solution that works:

=day&vlookup(day,vlookup_table,2,false)

vlookup_table
1 st
2 nd
3 rd
4 th
5 th
6 th
7 th
8 th
9 th
10 th
11 st
12 nd
13 rd
14 th
15 th
16 th
17 th
18 th
19 th
20 th
21 st
22 nd
23 rd
24 th
25 th
26 th
27 th
28 th
29 th
30 th
31 st
  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 538
Default formatting numbers as 1st 2nd

chiacheng.teg wrote:

On Friday, February 13, 2009 at 5:10:01 AM UTC+8, Bob Arnett wrote:
Is there any way to display numbers in the mode of:
1st
2nd
3rd
4th
etc. ?


Did you not notice that the original post was ***NINE ****ING YEARS AGO***?!
****ing Google Groupies. I wish Google would disable replies after a few days
of no activity.

Or a primitive but simple solution that works:

=day&vlookup(day,vlookup_table,2,false)


A solution that doesn't require manual entry of every number in existence:

=A1&IF(OR(AND(A13,A1<21),A1=0),"th",VLOOKUP(MOD(A 1,10),vlookup_table,2))

....which only requires a 5-line lookup table:

1 st
2 nd
3 rd
4 th
* th

....or, you know, the shorter and much better solution provided by Rick
Rothstein 16 minutes after the original post -- again, ***NINE YEARS AGO***:

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)2)+1),2)

--
You think you have won. We shall see about that.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting Numbers Jayant Gedam Excel Discussion (Misc queries) 1 October 27th 07 05:58 PM
Formatting Numbers Peledon New Users to Excel 3 April 11th 07 06:04 PM
Formatting numbers KC8DCN Excel Discussion (Misc queries) 2 May 24th 06 05:53 AM
formatting numbers Pascale Excel Discussion (Misc queries) 2 January 24th 06 12:05 PM
Formatting numbers avjunior Excel Discussion (Misc queries) 7 October 25th 05 07:47 PM


All times are GMT +1. The time now is 01:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"