Create ordinal number format in Excel

Anonymous

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

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

Anonymous

2011-04-16T08:41:09+00:00

I think you should change this line of code...

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

to this...

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

so that the user doesn't have to type the function name with the exact same casing you specified in your If..Then test.
Was this answer helpful?

0 comments No comments
Paul Edstein 82,871 Reputation points Volunteer Moderator

2011-04-16T12:50:30+00:00

Hi Rick,

Good point. I'll edit the original.
Was this answer helpful?

0 comments No comments
Anonymous

2011-04-16T14:15:49+00:00

By the way, it was early in the wee hours of the morning (just before I went to bed) when I posted my message and forgot to mention how much I liked your hybrid "UDF/Event Code" solution... that is an excellent method to circumvent the UDF restriction of not being able to take any actions other than returning a value to its host cell... I realy liked it!
Was this answer helpful?

0 comments No comments
Paul Edstein 82,871 Reputation points Volunteer Moderator

2011-04-16T21:32:40+00:00

I'm glad to hear a humble Word MVP can contribute something useful for you Excel geeks!!!
Was this answer helpful?

0 comments No comments
Anonymous

2011-04-18T06:13:35+00:00

I realize this is an old discussion that somehow took on a new life...

Here's my take on a Excel 2007 or later solution...

Ordinal Number Suffixes

http://www.tushar-mehta.com/publish_train/xl_vba_cases/0122%20ordinal%20number%20suffixes.shtml

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
Was this answer helpful?

0 comments No comments