Create ordinal number format in Excel

Okay so I've resolved this, for others who have this issue in future.

First option:

This will require two cells, type the number or date in A1 and change it using a formula in A2 which will return the ordinal number version of A1 in A2 (Of course, you can edit the formula to use other cell references).

The formula below innately works for data in Number format, but if you wish to use the formula on a date then substitute "A1" for "DAY(A1)".

Here's the formula:

=A1&IF(AND(MOD(ABS(A1),100)>=10,MOD(ABS(A1),100)<=14),"th", CHOOSE(MOD(ABS(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

As this formula converts a cardinal number into ordinal text, to retrieve the number from the helper cell formula result of cell A2, please use the following formula:

=LEFT(A2,LEN(A2)-2)

Second option: (The better resolve, in my opinion, albeit more tedious to implement)

This resolve requires use of conditional formatting and does not require a helper cell, as it will format each cell value governed by the conditional formatting rule without changing the stored data.

If you wish to use the conditional formatting on Date values rather than Numbers, then substitute "A1" for "TEXT(A1,"d")" in the following formulas.

Just to note, you will need to substitute "A1" for a cell reference governed by the range of values you wish to use the conditional formatting for. For example, if you wish to apply the following rules to the range "B3:D5" then you could use the cell reference "C4" in the following formulas, substituting it for "A1".

Please add the rules in the following order:

Conditional Formatting Rule 1, which works for numbers/days 4-9, by appending "th":

Conditional Formula:  =AND(MOD(ABS(A1),10)>3,MOD(ABS(A1),10)<10)

Number Format:         0"th"

Conditional Formatting Rule 2, which works for number/day 0, by appending "th":

Conditional Formula:  =MOD(ABS(A1),10)=0

Number Format:         0"th"

Conditional Formatting Rule 3, which works for number/day 1, by appending "st":

Conditional Formula:  =MOD(ABS(A1),10)=1

Number Format:         0"st"

Conditional Formatting Rule 4, which works for number/day 2, by appending "nd":

Conditional Formula:  =MOD(ABS(A1),10)=2

Number Format:         0"nd"

Conditional Formatting Rule 5, which works for number/day 3, by appending "rd":

Conditional Formula:  =MOD(ABS(A1),10)=3

Number Format:         0"rd"

Conditional Formatting Rule 6, which works for numbers/days 11-13, by appending "th":

Conditional Formula:  =AND(MOD(ABS(A1),100)>10,MOD(ABS(A1),100)<14)

Number Format:         0"th"

All done!

Happy formatting!!

Maria

Sources:

https://www.bcti.com/index.php/2017/06/30/excel-adding-ordinal-suffixes-list-numbers/

https://www.techrepublic.com/blog/windows-and-office/pro-tip-add-an-ordinal-indicator-to-a-value-in-excel/

http://www.meadinkent.co.uk/xltextabs.htm

Okay so I've resolved this, for others who have this issue in future.

First option:

This will require two cells, type the number or date in A1 and change it using a formula in A2 which will return the ordinal number version of A1 in A2 (Of course, you can edit the formula to use other cell references).

The formula below innately works for data in Number format, but if you wish to use the formula on a date then substitute "A1" for "DAY(A1)".

Here's the formula:

=A1&IF(AND(MOD(ABS(A1),100)>=10,MOD(ABS(A1),100)<=14),"th", CHOOSE(MOD(ABS(A1),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

As this formula converts a cardinal number into ordinal text, to retrieve the number from the helper cell formula result of cell A2, please use the following formula:

=LEFT(A2,LEN(A2)-2)

Second option: (The better resolve, in my opinion, albeit more tedious to implement)

This resolve requires use of conditional formatting and does not require a helper cell, as it will format each cell value governed by the conditional formatting rule without changing the stored data.

If you wish to use the conditional formatting on Date values rather than Numbers, then substitute "A1" for "TEXT(A1,"d")" in the following formulas.

Just to note, you will need to substitute "A1" for a cell reference governed by the range of values you wish to use the conditional formatting for. For example, if you wish to apply the following rules to the range "B3:D5" then you could use the cell reference "C4" in the following formulas, substituting it for "A1".

Please add the rules in the following order:

Conditional Formatting Rule 1, which works for numbers/days 4-9, by appending "th":

Conditional Formula:  =AND(MOD(ABS(A1),10)>3,MOD(ABS(A1),10)<10)

Number Format:         0"th"

Conditional Formatting Rule 2, which works for number/day 0, by appending "th":

Conditional Formula:  =MOD(ABS(A1),10)=0

Number Format:         0"th"

Conditional Formatting Rule 3, which works for number/day 1, by appending "st":

Conditional Formula:  =MOD(ABS(A1),10)=1

Number Format:         0"st"

Conditional Formatting Rule 4, which works for number/day 2, by appending "nd":

Conditional Formula:  =MOD(ABS(A1),10)=2

Number Format:         0"nd"

Conditional Formatting Rule 5, which works for number/day 3, by appending "rd":

Conditional Formula:  =MOD(ABS(A1),10)=3

Number Format:         0"rd"

Conditional Formatting Rule 6, which works for numbers/days 11-13, by appending "th":

Conditional Formula:  =AND(MOD(ABS(A1),100)>10,MOD(ABS(A1),100)<14)

Number Format:         0"th"

All done!

Happy formatting!!

Maria

Sources:

https://www.bcti.com/index.php/2017/06/30/excel-adding-ordinal-suffixes-list-numbers/

https://www.techrepublic.com/blog/windows-and-office/pro-tip-add-an-ordinal-indicator-to-a-value-in-excel/

http://www.meadinkent.co.uk/xltextabs.htm

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)

use below formula;

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