# Thread: excel add st nd rd th to number Thanks: 0 Likes: 0

1. ## excel add st nd rd th to number

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

2. ## Re: excel add st nd rd th to number

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")`

3. ## Re: excel add st nd rd th to number

perfect! thank you

4. ## Re: excel add st nd rd th to number

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.

5. ## Re: excel add st nd rd th to number

Originally Posted by 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
Here's another one...

A2 = some number

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

6. ## Re: excel add st nd rd th to number

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

7. ## Re: excel add st nd rd th to number

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

8. ## Re: excel add st nd rd th to number

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

9. ## Re: excel add st nd rd th to number

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

10. ## Re: excel add st nd rd th to number

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•