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
    2011-02-27T22:11:55+00:00

    Select your values and try this little macro:

    Sub ordination(r As Range)

    Dim s As String

    s = ""

    dq = Chr(34)

    ar = Array("th", "st", "nd", "rd", "th", "th", "th", "th", "th", "th")

    With r

        .ClearFormats

        v = .Value

        If v > 10 And v < 20 Then

            s = "th"

        Else

            s = ar(v Mod 10)

        End If

        .NumberFormat = "General" & dq & s & dq

    End With

    End Sub

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-02-27T22:35:51+00:00

    Originally I was going to respond to Adam Vero (the last poster before you) noting that his response comes some 6 months after the previous last posting in this thread, but then noticed that you just posted a macro in response. Given that, I thought I would direct your attention to the event code I posted to this thread back in August 2010... I thought you might find my one-liner number formatter of some interest.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-02-28T02:09:23+00:00

    Rick:

    Very interesting.

    It took me a while go to thru the formula and map its logic.

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

    Was this answer helpful?

    0 comments
  4. 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
  5. Anonymous
    2011-02-28T15:22:27+00:00

    Had this been last week I would have proposed Adam's Solution as the answer.

    Very nice solution Adam, as it leaves the numeric values intact.

    Was this answer helpful?

    0 comments