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: Oldest
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2014-06-29T08:47:05+00:00

    I had a list of 100 and that I had to select the first 10, the remainder are non-qualifiers. 

    the remainder should show blank.

    As all your formulas show the ordinals,  I take the shortest and improve it a little. This one shows only the ordinal numbers from 1 to 10:

    =IF(OR(A1<=0,A1>10),"",A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2))

    Andreas.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2014-06-29T15:32:14+00:00

    Thanks Andreas

    Eureka!!  Thanks for that.  It works!

    Daleman9

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-02-03T14:57:16+00:00

    Suppose the number is X

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

    Regards

    Brian

    Was this answer helpful?

    0 comments
  4. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2015-02-04T03:24:35+00:00

    My reply of April 28, 2014 applies to both your post and that of all the others that output the ordinal in a different cell. That is, they don't meet the OP's specification:

    have the number display as an ordinal number*(e.g., "1st", "2nd", "3rd", etc.) while still retaining its numeric value (1, 2, 3, etc.).*

    Was this answer helpful?

    0 comments
  5. Anonymous
    2015-02-04T04:29:18+00:00

    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.

    Was this answer helpful?

    0 comments