1st, 2nd, 3rd with Text

[albert5445]

http://www.excelforum.com/excel-gene...d-3rd-etc.html

Hello,

I found the above post very helpful and would like to know if it would be possible to do the following

I would like an Excel field that says something along the lines of "John finished 3rd with a time of 4:50."
I can link 'John', '3', and '4:50' from another matrix/table but would it be possible to include a formula to automatically put the 'rd' after 3 (or a 'th' after 5, etc)?

Thanks for any help/suggestions offered!

Albert

[Paul]

Hi Albert, welcome to the forum.

Perhaps by combining one of the formulas from that other post...

Let's assume John is in A1, 3 is in A2 and 4:50 is in A3, then use:

=A1&" finished "&A2&IF(OR(MOD(A2,100)={11,12,13}),"th",CHOOSE(MIN(5,RIGHT(A2)+1),"th","st","nd","rd","th"))&" with a time of "&TEXT(A3,"h:mm")&"."