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: Most helpful
Most helpful Newest Oldest
  1. 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
  2. Anonymous
    2011-02-28T02:15:56+00:00

    It can probably form the basis of a worksheet formula to give a string result rather than a format.

    Already done....

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

    I posted a direct conversion of my VB code in answer to a question awhile ago and Biff noted that is was the shortest formula for ordinals he had seen at the time... he then proceded to tighten it up until the above resulted. Biff believes it is the shortest possible Excel formula to do ordinals (at least to date).

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2010-08-09T16:25:06+00:00

    Here is some event code which will handle the changing of the cell or cells number format automatically...

    Private Sub Worksheet_Change(ByVal Target As Range)

      Dim Cell As Range

      For Each Cell In Target

        If Cell.Column = 3 And IsNumeric(Cell.Value) Then

          Cell.NumberFormat = "#""" & Mid$("thstndrdthththththth", 1 - 2 * ((Cell.Value) _

                              Mod 10) * (Abs((Cell.Value) Mod 100 - 12) > 1), 2) & """"

        End If

      Next

    End Sub

    To install this code, right click the name tab at the bottom of the worksheet you want to have this functionality, select View Code from the popup menu that appears and then copy/paste the above code into the code window that appeared. Here I have set the code to respond to numbers placed in Column "C", but that can be changed, of course. Now, whenever you enter a number into Column "C" (or whatever column you choose to monitor), that number will display with the ordinal suffix attached to it, but the value in the cell will be the number itself.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2010-08-09T16:09:33+00:00

    Jim,

    I would prefer to use a macro or some VBA code, rather than an adjacent column.

    One solution I have worked out is to simply format the ordinal numbers as text strings, and then when I want to perform operations on them (such as MIN()), I can extract the numeric part of the string, convert to a number, and use arrays.  But this seems a little clumsy and I am wondering if there is a more elegant way to do it.

    I am surprised that Excel does not have ordinal numbers built in!

    Thanks,

    Craig

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  5. 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