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

 

[barry houdini]

If you just want 3rd, 4th, 5th etc. then you can take the TEXT function off the end, just use this part

=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"}))

 

[T. Valko]

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

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

If you're using Excel 2007 or later you can create custom ordinal formats.

See this:

http://www.mrexcel.com/forum/showpost.php?p=3039756&postcount=15

 

[HJay]

Brill I have it working now I thought I was going to have to type a years worth of dates out by hand!

 

[HJay]

If you're using Excel 2007 or later you can create custom ordinal formats.

See this:

http://www.mrexcel.com/forum/showpost.php?p=3039756&postcount=15

wooooaahhh That thread is way way beyond me I am afraid, really don't understand why in cell formatting you can't just click on an option for 'th'

 

[T. Valko]

wooooaahhh That thread is way way beyond me I am afraid, really don't understand why in cell formatting you can't just click on an option for 'th'

Well, you'd have to ask the folks at Microsoft why there isn't a built-in format to do that.

Seems like a fairly common thing that people would want to do so it should be a built-in feature.

 

[barry houdini]

It's not possible to do with formatting.

Thanks Biff

 

[T. Valko]

Thanks Biff

 

[grd]

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

Could I suggest it is not good grammar to use st, nd, rd etc when writing dates unless you are writing the date within a sentence. eg Christmas day is on the 25th of December. Note the "of".