Formatting Ordinal Numbers

[inarbeth]

Is there a way to format numbers (1 to 31 for the days of the month or more if possible) 1st, 2nd, 3rd, 4th, 5th etc. I can't get Excel to accept more than three
Custom formats:[=1]##"st";[=2]##"nd";##"th"

Can this be done with VBA?

 

[inarbeth]

On 2002-06-23 17:12, Yogi Anand wrote:
Hi inarbeth:

I am not sure if my proposed solution will work for you -- I have the following macro:

Private Sub CommandButton1_Click()

' created 6/23/02 by Yogi Anand

Range("A1").Select
If [A1]<= 31 Then Selection.NumberFormat = "[<32]##""th"";General"
If [A1] = 1 Then Selection.NumberFormat = "[=1]##""st"";General"
If [A1] = 2 Then Selection.NumberFormat = "[=2]##""nd"";General"
If [A1] = 3 Then Selection.NumberFormat = "[=3]##""rd"";General"
If [A1] = 21 Then Selection.NumberFormat = "[=21]##""st"";General"
If [A1] = 22 Then Selection.NumberFormat = "[=22]##""nd"";General"
If [A1] = 23 Then Selection.NumberFormat = "[=23]##""rd"";General"


End Sub

I created a CommandButton on a worksheet of a workbook and I assigned the above written macro to the command button.
I make my number entry in cell A1 (valid entries are 1 to 31)
Then I click on the CommandButton to see

1 formatted to 1st
2 formatted to 2nd
3 formatted to 3rd
.......

The numeric value of the entry stays intact, it is just formatted as 1st, 2nd, 3rd, ...

Hope This Helps

Regards!


Edit Note: I have not fully tested it -- some tweaking may be necessary -- you may also have to tweak it to adapt it to your needs. I would have gone strictly for CUSTOM formatting only without the benefit of a macro, but as you correctly pointed out, one cannot accommodate all the CUSTOM formats natively.
This message was edited by Yogi Anand on 2002-06-23 17:16

Thanks Yogi
I have modified the code to this:

Private Sub CommandButton1_Click()
Selection.Select
If Selection<= 31 Then Selection.NumberFormat = "[<32]##""th"";General"
If Selection = 1 Then Selection.NumberFormat = "[=1]##""st"";General"
If Selection = 2 Then Selection.NumberFormat = "[=2]##""nd"";General"
If Selection = 3 Then Selection.NumberFormat = "[=3]##""rd"";General"
If Selection = 21 Then Selection.NumberFormat = "[=21]##""st"";General"
If Selection = 22 Then Selection.NumberFormat = "[=22]##""nd"";General"
If Selection = 23 Then Selection.NumberFormat = "[=23]##""rd"";General"


End Sub

This works on one cell at a time. Is there an easy modification to allow selection of multiple cells?

 

[Ivan F Moala]

Try

<pre/>
Private Sub CommandButton1_Click()
Dim oCell As Range

For Each oCell In Selection
Select Case oCell
Case Is <= 31: Selection.NumberFormat = "[<32]##""th"";General"
Case Is = 1: Selection.NumberFormat = "[=1]##""st"";General"
Case Is = 2: Selection.NumberFormat = "[=2]##""nd"";General"
Case Is = 3: Selection.NumberFormat = "[=3]##""rd"";General"
Case Is = 21: Selection.NumberFormat = "[=21]##""st"";General"
Case Is = 22: Selection.NumberFormat = "[=22]##""nd"";General"
Case Is = 23: Selection.NumberFormat = "[=23]##""rd"";General"
End Select
Next


End Sub
</Pre>

 

[inarbeth]

Hi Ivan
The code only returns the th suffix, ie 21th, 22th, 23th etc.

 

[cobdElug]

Use this select case list instead :-

Case Is = 1: oCell.NumberFormat = "[=1]##""st"";General"
Case Is = 2: oCell.NumberFormat = "[=2]##""nd"";General"
Case Is = 3: oCell.NumberFormat = "[=3]##""rd"";General"
Case Is = 21: oCell.NumberFormat = "[=21]##""st"";General"
Case Is = 22: oCell.NumberFormat = "[=22]##""nd"";General"
Case Is = 23: oCell.NumberFormat = "[=23]##""rd"";General"
Case Is = 31: oCell.NumberFormat = "[=31]##""st"";General"
Case Is < 31: oCell.NumberFormat = "[<31]##""th"";General"

 

[inarbeth]

Thanks that does the trick.

 

[Ivan F Moala]

I guess i should have tested it 1st....but then I took it from your code that you said worked