Conditional Formatting for Ordinals

I use this formula to display finishing positions in Ordinals in column “I”:-

=IF(LEN(B3)<>5,"",IF(F3=0,0,IF(AND(RANK(F3,Final_Total,1)<14,RANK(F3,Final_Total,1)>10),RANK(F3,Final_Total,1)&“th”,RANK(F3,Final_Total,1)&CHOOSE(MOD(RANK(F3,Final_Total,1),10)+1,“th”,“st”,“nd”,“rd”,“th”,“th”,“th”,“th”,“th”,“th”))))

Note - column B are minutes and secs entered as MM.SS; makes for easier input on a numerical keypad as a simple formular displays the time in HH:MM:SS in another column or other text if competitor Did not start or finish

However I would prefer for the cells in the column “I” to contain just numbers so that I can sort on that column, but use conditional formatting to display the numbers as Ordinals.

How can this be achieved?

Any advice welcomed.

Please look at
Number Format Codes
and search at the bottom for NatNum12 modifier

1 Like