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. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2016-01-05T22:53:06+00:00

    I really have no idea what you're on about. First you complain "I've tried Shift + F9 but it didnt do anything", now you're saying "His answer was to press Alt + F4 which turns the job off". Yet you're the only person to have referred to either of these key combinations in this thread.

    If you want to get a result, you really should pay attention to the advice given...

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-01-06T03:43:52+00:00

    My mistake in what I am asking help for.   I have a list of names and addresses. Most have addresses like 1st street, 2nd avenue, etc.  What I am looking for is to make the nd, st in superscript in Excel.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-06-07T11:22:57+00:00

    use below formula;

    =A1&""&IF(MOD(A1,10)=0,"th",CHOOSE(MOD(A1,10),"St","nd","rd","th","th","th","th","th","th"))

    Was this answer helpful?

    0 comments
  5. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2016-06-07T12:35:48+00:00

    If you read the first post in this thread, you will see that your 'solution' does not address the OP's needs:

    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.).

    Furthermore, your 'solution' does not handle numbers ending in 11, 12, or 13, properly. To do that with a formula-based solution, try something like:

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

    Was this answer helpful?

    0 comments