Create ordinal number format in Excel

From a relative Excel neophyte....

Assuming "J4" is the cell with the number you want to convert to ordinal format, I used this:

=J4&IF(OR(RIGHT(J4,2)="11",RIGHT(J4,2)="12",RIGHT(J4,2)="13"),"th",CHOOSE(RIGHT(J4,1),"st","nd","rd","th","th","th","th","th","th","th"))

Since I'm not too familiar with the MOD function or with the { } array type stuff in a formula and since I'm far from an Excel MVP, I just used the "brute force" method, which so far seems to work ok.

Hope this is helpful.

Peter

Hey Rick,

Thanks for the formula. Is there a way to make this apply to multiple columns? I am a gymnastics coach and I have created a Meet Results Sheet that I would like to be able to plug in where they finished on each event.

Thanks,

Sean

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

Here's a formatting trick from the prolific TK Anthony (creator of Real Times,****the world's first electronic magazine on the The Source network).

= = =

There once was an Excel ordinal formatting code in an ancient version that may or may not have been released.  However, with the advent of conditional formatting in later versions, you can implement a true format method for ordinal suffixes.  

Summary of Method: 

This method creates 4 conditional formatting rules that will add the appropriate ordinal suffix to a number.

To do this and to test how it works, I suggest the following steps.

1. Create a vertical list of sequential test numbers, 0-30, to test with. 

It is easy to do this if you enter "0" in the topmost cell and "1" in the next lower one.  Now highlight both cells then use the lower right corner anchor to drag downward.  This creates an auto-fill of sequential numbers.  Stop when you get to 30.  That's as high as you need to test the effects of the formatting. 2. Highlight the entire vertical list of sequential numbers. 3. Click on Conditional Formatting in the ribbon bar of the [Home] menu tab. 4. Click on Manage Rules 5. Click on New Rule to create a new set of format rules for the selected cell. 6. Click on Use a formula to determine which cells to format.

Nb. The above option title is incorrect.  It should read,

"Use a formula to describe a condition that triggers the format." 7. We enter the 1st of 4 format rules that will yield ordinal formatting.

Nb. Since the last entered rule is the first, we entered them in reverse order. 8. Rule 4: Catch all "th" format

Set Condition: =True

Nb. =True will always trigger so, as the last rule, it is the default.

Set Format: Click Format, [Number] tab, then Custom.

Enter #,##0\t\h in the Type: box, then click OK (twice). 9. Back at the rules list, we click New Rule again, then Use a formula ... 10. Rule 3: The "rd" suffixes.

Set Condition: =AND(A1<>13,MOD(A1,10)=3)

Nb. Replace the 2 "A1"s with the address of the 1st cell of your list.

Make it relative so leave out any dollar signs ($).

Click Format, [Number] tab, then Custom.

Set Format: #,##0\r\d in the Type: box, then click OK (twice). 11. Rule 2: We repeat above steps for "nd" ordinal:

Set Condition: =AND(A1<>12,MOD(A1,10)=2)

Set Format: #,##0\n\d 12. Rule 1: We repeat above steps for "st" ordinal:

Set Condition: =AND(A1<>11,MOD(A1,10)=1)

Set Format: #,##0\s\t 13. Check Stop If True for ALL FOUR rules.

Nb. This is IMPORTANT since the last "st" rule is a catch-all that assumes it will not be executed if a higher rule already triggered.  Otherwise, all numbers would end up with the "st" ordinal when the last rule was always executed.  You could also set the condition of the "st" rule to be =Mod(A1,10)=0 but it is more efficient to stop further condition tests as soon as a rule has triggered. 14. Your list is now conditionally formatted to add the proper ordinal suffix to each cell in that list.  Just look at it as you add your rules to see that it is working correctly as you enter each rule. 15. Portability:

To copy your coded rules to another cell or range, do a Right-Click, Copy on one of the cells you formatted then a Right-Click, Paste Special, Formatting onto the destination cell or range.  Voila. 16. Caveat:

The only minor downside (apart from making your fingers work to hard) is that conditional formatting overrides other numeric formatting.  E.g., if you wanted to set a $ format to a number than has this conditional formatting, nothing will happen when you apply the $ format.   Other format types like size, color, and bold, may still be used since they are not included in the conditional format.

Life is finite. Take pleasure now!

TK Anthony

Hi Tushar,

Such an approach had been suggested earlier in the thread. Sadly, it doesn't work with pre-2007 versions of Excel. Hence the UDF/macro solutions.

I realize this is an old discussion that somehow took on a new life...

Here's my take on a Excel 2007 or later solution...

Ordinal Number Suffixes

http://www.tushar-mehta.com/publish_train/xl_vba_cases/0122%20ordinal%20number%20suffixes.shtml

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