excel add st nd rd th to number

[proctk]

I want to and st nd rd th to all the values found in a column, what's the easiest way without using a macro. I can easily add a hidden column

 

[Michael M]

Found this in an old thread.
Don't know how accurate it is, untested

=A1&CHOOSE(AND(A1<>{11,12,13})*MIN(4,MOD(A1,10))+1,"th","st","nd","rd","th")

 

[proctk]

perfect! thank you

 

[Ron Coderre]

The previously posted formula fails for each 11, 12, 13 variation, beginning with 111, 112, and 113. If that's an issue, this variation will work for any value:

=A1&" "&CHOOSE(AND(MOD(A1,100)<>{11,12,13})*MIN(4,MOD(A1,10))+1
,"th","st","nd","rd","th")

I hope that helps.

 

[T. Valko]

I want to and st nd rd th to all the values found in a column, what's the easiest way without using a macro. I can easily add a hidden column

Here's another one...

A2 = some number

=A2&" "&MID("thstndrdth",MIN(9,2*RIGHT(A2)*(ABS(MOD(A2,100)-12)>1)+1),2)

 

[HJay]

Hi

I also want to be able to do this but I don't understand what you are supposed to do with the code? I had hoped to be able to just format the date to show th st rd but that doesn't seem possible Any help appreciated

 

[barry houdini]

Hello HJay, welcome to MrExcel

It's not possible to do with formatting. All the suggestions here assume you have a date in one cell and put the formula in an adjacent cell to show that date in the required format

 

[HJay]

Hi Barry

Thanks for the reply, I have a list of dates in the format 3 Sept, 4 Sept, 5 Sept etc. what I want to do is convert that to say 3rd, 4th, 5th. I need the dates for a label in a chart, so I think I need to convert it to text rather than date. Is there an easy way to do this? I am not even really sure of what keywords to google

Many thanks

HJay

 

[barry houdini]

Well if you have a valid date in A2 you can make it into 3rd Sep 2012 etc. in B2 with this formula

=IF(A2="","",DAY(A2)&LOOKUP(DAY(A2),{1,2,3,4,21,22,23,24,31;"st","nd","rd","th","st","nd","rd","th","st"})&TEXT(A2," mmm yyyy"))

 

[HJay]

Brill that's great, how do I hide the month and the year?