excel add st nd rd th to number

proctk

Well-known Member
Joined
Dec 24, 2004
Messages
840
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Found this in an old thread.
Don't know how accurate it is, untested
Code:
=A1&CHOOSE(AND(A1<>{11,12,13})*MIN(4,MOD(A1,10))+1,"th","st","nd","rd","th")
 
Upvote 0
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:
Code:
=A1&" "&CHOOSE(AND(MOD(A1,100)<>{11,12,13})*MIN(4,MOD(A1,10))+1
,"th","st","nd","rd","th")

I hope that helps.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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"))
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top