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. Anonymous
    2010-08-09T15:11:22+00:00

    go into formatting and pick "custom"

    write 0"th" for numbers 4 and up, and 0"st", 0"nd", 0"rd" for numbers 1, 2 and 3 respectively.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2010-08-09T15:36:53+00:00

    I am curious how you make that work. I am not having any luck at my end (XL2003) and here is what my help says.

    You can specify up to four sections of format codes. The format codes, separated by semicolons, define the formats for positive numbers, negative numbers, zero values, and text, in that order. If you specify only two sections, the first is used for positive numbers and zeros, and the second is used for negative numbers. If you specify only one section, it is used for all numbers. If you skip a section, include the ending semicolon for that section.

    Also I am not sure how you will handle numbers 10 and up?

    If this post answers your question, please mark it as the Answer... Jim Thomlinson

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-08-09T15:42:05+00:00

    You can pretty easily write a macro

    Dim m as Range, myRange as Range

    For Each m in myRange

    m.NumberFormat = "0""th"""

    m.NumberFormat = "0""rd"""

    m.NumberFormat = "0""nd"""

    m.NumberFormat = "0""st"""

    Next

    With conditionals for each option.  Everything from 4 through 20 would be "th", and everything after that would follow the same pattern as 0 through 10.  So it'd be five cases or if statements.

    Alternatively you can do it within the worksheet_change sub.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2010-08-09T15:47:56+00:00

    There is no simple solution to this. You can either use an adjascent column or you can use a macro. Depends which direction you want to go. We can help you with either way... What would be your preference?

    If this post answers your question, please mark it as the Answer... Jim Thomlinson

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  5. Anonymous
    2010-08-09T15:55:59+00:00

    With

    A1: a number....eg 101

    This formula returns the ordinal version of that number

    B1: =A1&" "&MID("thstndrdthstndrdth",MATCH(IF(MOD(A1,100)>29,MOD(A1,10)+20,MOD(A1,100)),{0,1,2,3,4,21,22,23,24},1)*2-1,2)

    In the above example, the formula returns: 101 st

    Does that help?

    Ron Coderre

    Microsoft MVP (2006 - 2010) - Excel

    P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)

    Was this answer helpful?

    10 people found this answer helpful.
    0 comments