Create ordinal number format in Excel

Anonymous
2010-08-09T14:55:38+00:00

Hello,

I am wondering if anyone knows (or knows of any resources that describe) how to create an ordinal number format in MS Excel.

I am well aware of how to reformat numbers as text to create the ordinal number effect, but what I would like to do is have the numberdisplay as an ordinal number (e.g., "1st", "2nd", "3rd", etc.) while still retaining its numeric value (1, 2, 3, etc.).

The reason for this request is to be able to perform numeric functions (e.g., MIN, MAX, AVERAGE, etc.) on an array of ordinal numbers.

I have a spreadsheet that lists various athletes' scores and the ranking for each score.  I would like to be able to easily determine, for example, each athlete's best ranking by using a MIN() function on the list of ordinal numbers.  Currently the only way I can think to do this is by having the rankings as regular cardinal numbers.

I suppose it would be possible to have an adjacent, hidden column with the numeric value of each ranking, but I thought it might be more elegant if there was a way to change the format of the number without changing its value.

Any help is much appreciated!

Regards,

Craig

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

58 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2015-12-11T00:40:00+00:00

    I'm sure you are joking. If you are serious I would report this as abuse.

    I asked you where in the mail merge date field do I put the code.  I can't open any mail merge field.

    I've tried Shift + F9 but it didnt do anything.

    Was this answer helpful?

    0 comments
  2. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2015-12-10T20:55:47+00:00

    It's all explained in the post you replied to...

    Cross-posted at: http://www.mrexcel.com/forum/excel-questions/907925-mail-merge-date-format.html

    For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-12-10T13:57:54+00:00

    Where in the mail merge date field do I put this

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

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

    Was this answer helpful?

    0 comments
  4. Anonymous
    2015-11-20T22:21:39+00:00

    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.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  5. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2015-09-10T00:32:54+00:00

    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.

    Was this answer helpful?

    0 comments