formatting numbers as 1st 2nd
 

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

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. ?

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. ?

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"}))

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. ?

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. ?

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"}))

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. ?

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

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)

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

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.

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)

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)

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!

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

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.