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: Newest
Most helpful Newest Oldest
  1. Anonymous
    2015-09-09T23:27:11+00:00

    Using some previous posts in this thread I have created a formula to convert a date into the format ""2nd September 2015"

    I needed it in this exact format for adding a date into a mail merge from excel. "Your appointment has been cancelled on 2nd September 2015".

    Just right clicking and changing the cell format was not adequate as it messed up my mail merge somehow.

    the date "02-Sep" format was in L4

    put this in L5:

    =MID(TEXT(DATEVALUE(L4),"d/m/yy"),1,1)&MID("thstndrdthstndrdth",MATCH(IF(MOD(MID(TEXT(DATEVALUE(L4),"d/m/yy"),1,1),100)>29,MOD(MID(TEXT(DATEVALUE(L4),"d/

    put this in L6:

    =TEXT(DATE(2000,MID(TEXT(DATEVALUE(L4),"d/m/yy"),3,1),1),"mmmm")

    put this in L7:

    ="20"&MID(TEXT(DATEVALUE(L4),"d/m/yy"),5,2)

    You can then combine these 3 formulas in any way you want. I hope someone, somewhere also finds this useful as it took me a while.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-02-04T15:42:49+00:00

    Ok, I see.

    Note: your Change event VBA produces errors if the cell is empty or contains text or is a negative number.

    Note: With B1=Ordinal(A1) it works. Then if you change the value in A1, the change event does not address the cell B1 - the Target is A1 and the ordinal suffix is not changed. Therefore the routine should be:-

    Private Sub Worksheet_Change(ByVal Target As Range)

        Dim Cell As Range

        On Error Resume Next

        For Each Cell In ActiveSheet.UsedRange

          If UCase(Cell.Formula) Like "=ORDINAL(?*)" Then

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

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

          End If

        Next Cell

    End Sub

    Regards

    Brian

    PS A correction to my formula for numbers >100:

    =IFERROR(IF(MOD(A1,1),"",MID("thstndrdth",2*(MIN(MOD(A1*AND(--RIGHT(A1,2)<>{13,12,11}),10),4))+1,2)),"")

    Was this answer helpful?

    0 comments
  3. Paul Edstein 82,871 Reputation points Volunteer Moderator
    2015-02-04T06:13:13+00:00

    I think you're missing the point that a number of posts in this thread have shown how ordinal formatting of the result can be done and retain the underlying value as a number - just as the OP specified. That can be achieved using either conditional formatting in Excel 2007 & later or a custom UDF (per my post of April 16, 2011) in any Excel version.

    Was this answer helpful?

    0 comments
  4. 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
  5. 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