=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)
--
Rick (MVP - Excel)
"Bob Arnett" <BobA...@discussions.microsoft.com> wrote in message
news:49A5E465-C6A1-42E6...@microsoft.com...
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
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"}))
--
Rick (MVP - Excel)
"Glenn" <add...@not.valid> wrote in message
news:uYTkYwVj...@TK2MSFTNGP04.phx.gbl...
--
Rick (MVP - Excel)
"dl...@uafc.com" <dlotzu...@discussions.microsoft.com> wrote in message
news:643964E2-6114-4F3C...@microsoft.com...
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
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
'====================================
>
>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(A1,"dd"),2),
10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
Note: This is made for European date format.
>>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.