Create ordinal number format in Excel

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.

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.

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)),"")

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.

Hi Paul

In Excel the number formatting is limited. The original number is in a cell, say A1, then in B1 place my formula (modified to prepend the number)

=IFERROR(A1&CHOOSE(MIN(MOD(A1*AND(A1<>{13,12,11}),10),4),"st","nd","rd"),"th")

This retains the original number in A1.

And you can also work with the numerical value of B1 by using the formula

=VALUE(LEFT(B1,LEN(B1)-2))

So yep, although the original number is not retained and displayed as an integer in the same cell, the above achieves a close workaround to the specs.

BTW the following formula guards against non integers and non numerics

=IFERROR(IF(MOD(A1,1),"",MID("

Regards

Brian

PS  My original post does actually come a little closer since the two cells are adjacent, with the left cell right justified and the right cel left justified, it looks like

10 th

Ok, with a gap in between, but you can format the B1 cell as a superscript to make it look realer.