It's not pretty, but it will work.

Code:

=IF(ISNUMBER(R5),1+COUNTIF($R$5:$R$24,">"&R5),"")&IF(AND(MOD(ABS(IF(ISNUMBER(R5),1+COUNTIF($R$5:$R$24,">"&R5),"")),100)>10,MOD(ABS(IF(ISNUMBER(R5),1+COUNTIF($R$5:$R$24,">"&R5),"")),100)<14),"th",CHOOSE(MOD(ABS(IF(ISNUMBER(R5),1+COUNTIF($R$5:$R$24,">"&R5),"")),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))

Should work to convert any cardinal number into an ordinal number. Note that these values will be stored as text and not as numbers anymore.

Alternatively, you could add the following into column T

Code:

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

This would leave the cardinal number in column S and add the ordinal suffix to column T which would aesthetically LOOK similar, but would allow the value in column S to be used as an actual number.

## Like this thread? Share it with others