B
Bob Arnett
Is there any way to display numbers in the mode of:
1st
2nd
3rd
4th
etc. ?
1st
2nd
3rd
4th
etc. ?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Rick Rothstein
Try this formula...
=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)
=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)
S
Shane Devenshire
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.
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.
G
Glenn
Rick said: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"}))
D
dlotz
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
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
D
dlotz
wow I have to check that formula out
Rick Rothstein said:Try this formula...
=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)
R
Rick Rothstein
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/micr...q=biff+ordinal+group:*excel*+author:rothstein
below and read messages 2 thru 11...
http://groups.google.com/group/micr...q=biff+ordinal+group:*excel*+author:rothstein
R
Rick Rothstein
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/micr...q=biff+ordinal+group:*excel*+author:rothstein
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/micr...q=biff+ordinal+group:*excel*+author:rothstein
R
Ron Rosenfeld
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
R
Rick Rothstein
Is there any way to display numbers in the mode of:
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
'====================================
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
'====================================
R
Ron Rosenfeld
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
N
NPell
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]*" Thenwhich makes sure that a non-digit is not located anywhere withinthe
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(A1,"dd"),2),
10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
Note: This is made for European date format.
R
Rick Rothstein
See my 2 inline comments...
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
Yeah... that is why I made the obfuscation comment and added the <g> tag to
it.
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
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.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.