number formatting - non standard

  • Thread starter Thread starter davlam
  • Start date Start date
D

davlam

Hi,

my mate has asked the fllowing question. the only solution i could ad
was to use the autofill drag option but that was not to his liking. hi
questions were..
HI m8,

In Excel, if I have cells containing the following

1
2
3

Is there a custom format command that will display the numbers as

1st
2nd
3rd

I can use the custom format #,##0"th" to give me

1th
2th
3th

(i.e. all the same) however I am sure there must be a format that wil
give

1st
2nd

etc.

Thanks in anticipation

Thanks for the reply.

Unfortunately, that recognises the cells as general format, rather tha
a custom format.

Altering my initial question slightly, is there any formula to ge
dates to display as 17th Sep 04 rather than just 17 Sep 04?

Cheers
James
Click to expand...

any thoughts?

cheers,

davla
 
No built-in Format.

See David McRitchie's site for working with ordinals in numbers and dates.

http://www.mvps.org/dmcritchie/excel/datetime.htm#ordinal

Also this code from Ron Rosenfeld...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
For Each cell In Target
If IsDate(cell.Value) Then
Select Case Day(cell.Value)
Case 1, 21, 31
cell.NumberFormat = "d""st"" mmm, yyyy"
Case 2, 22
cell.NumberFormat = "d""nd"" mmm, yyyy"
Case 3, 23
cell.NumberFormat = "d""rd"" mmm, yyyy"
Case 4 To 20, 24 To 30
cell.NumberFormat = "d""th"" mmm, yyyy"
End Select
End If
Next cell
End Sub

Note: Ron's code in placed in a worksheet module.


Gord Dibben Excel MVP
 
Try this:
=choose(a1,concatenate(a1,"st"))
Jerry
davlam said:
Hi,

my mate has asked the fllowing question. the only solution i could add
was to use the autofill drag option but that was not to his liking. his
questions were..


any thoughts?

cheers,

davlam
Click to expand...
 
Back
Top