Create ordinal number format in Excel

I think you're missing the point that a number of posts in this thread have shown how ordinal formatting of the result can be done and retain the underlying value as a number - just as the OP specified. That can be achieved using either conditional formatting in Excel 2007 & later or a custom UDF (per my post of April 16, 2011) in any Excel version.

Ok, I see.

Note: your Change event VBA produces errors if the cell is empty or contains text or is a negative number.

Note: With B1=Ordinal(A1) it works. Then if you change the value in A1, the change event does not address the cell B1 - the Target is A1 and the ordinal suffix is not changed. Therefore the routine should be:-

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Cell As Range

    On Error Resume Next

    For Each Cell In ActiveSheet.UsedRange

      If UCase(Cell.Formula) Like "=ORDINAL(?*)" Then

        Cell.NumberFormat = "#,#""" & Mid$("thstndrdthththththth", 1 - 2 * _

        ((Cell.Value) Mod 10) * (Abs((Cell.Value) Mod 100 - 12) > 1), 2) & """"

      End If

    Next Cell

End Sub

Regards

Brian

PS A correction to my formula for numbers >100:

=IFERROR(IF(MOD(A1,1),"",MID("thstndrdth",2*(MIN(MOD(A1*AND(--RIGHT(A1,2)<>{13,12,11}),10),4))+1,2)),"")

Using some previous posts in this thread I have created a formula to convert a date into the format ""2nd September 2015"

I needed it in this exact format for adding a date into a mail merge from excel. "Your appointment has been cancelled on 2nd September 2015".

Just right clicking and changing the cell format was not adequate as it messed up my mail merge somehow.

the date "02-Sep" format was in L4

put this in L5:

=MID(TEXT(DATEVALUE(L4),"d/m/yy"),1,1)&MID("thstndrdthstndrdth",MATCH(IF(MOD(MID(TEXT(DATEVALUE(L4),"d/m/yy"),1,1),100)>29,MOD(MID(TEXT(DATEVALUE(L4),"d/

put this in L6:

=TEXT(DATE(2000,MID(TEXT(DATEVALUE(L4),"d/m/yy"),3,1),1),"mmmm")

put this in L7:

="20"&MID(TEXT(DATEVALUE(L4),"d/m/yy"),5,2)

You can then combine these 3 formulas in any way you want. I hope someone, somewhere also finds this useful as it took me a while.

There is no need to change the Excel data in any way to have ordinals (including superscripting if you want it) in a mailmerge. Field coding in your Word mailmerge main document can do all the ordinal formatting for you! Besides which, once again, your solution requires outputting the result to a different cell, which is not what the OP asked for...

For Word mailmerge purposes, you could use a field coded as:

{MERGEFIELD MyDate @ "dddd 'the {MERGEFIELD MyDate @ d \*Ordinal} of' MMMM, yyyy"}

And, if you want superscripting of the ordinals, you could use a field coded as:

{QUOTE{SET MERGEDATE {MERGEFIELD MyDate}}{MERGEDATE @ "dddd 'the' d"}{IF{=(MOD({={MERGEDATE @ d}+89},100)>2)*(MOD({={MERGEDATE @ d}+9},10)<3)}= 1 {=MOD({MERGEDATE @ d},10)-2 # ^rd;st;nd^} ^th^}{MERGEDATE @ "' of' MMMM, yyyy"}}

where the 'rd', 'st', 'nd' and 'th' in the field code are all superscripted.

Alternatively, you could use a field coded as:

{QUOTE{SET MERGEDATE {MERGEFIELD MyDate}}{MERGEDATE @ "dddd 'the' d"}{^IF^{=(MOD({={DATE @ d}+89},100)>2)*(MOD({={MERGEDATE @ d}+9},10)<3)}= 1 {=MOD({MERGEDATE @ d},10)-2 # rd;st;nd} th \* Charformat}{MERGEDATE @ "' of' MMMM, yyyy"}}

where the 'IF' in the field code is superscripted.

Any of these will output the merged dates along the lines of 'Thursday the 10th of September, 2015', with/without superscripting. Other date formats are just as easily constructed.

Note: The field brace pairs (i.e. '{ }') for the above examples are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practicable to add them via any of the standard Word dialogues. The spaces represented in the field constructions are all required.

Just a quick thank you from 3 years in the future!

Found this thread while searching Bing for how to do this, and this worked perfectly for me.