Page 1 of 2 12 LastLast
Results 1 to 10 of 18

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

  1. #1
    Board Regular
    Join Date
    Dec 2004
    Location
    Cochrane, ON
    Posts
    837
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,315
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    2 Thread(s)

    Default 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")
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular
    Join Date
    Dec 2004
    Location
    Cochrane, ON
    Posts
    837
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel add st nd rd th to number

    perfect! thank you

  4. #4
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Boston, Massachusetts
    Posts
    2,285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (2006 - 2015)
    Using: Excel 2013 & 2016

  5. #5
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: excel add st nd rd th to number

    Quote Originally Posted by proctk View Post
    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)
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

  6. #6
    New Member
    Join Date
    Nov 2007
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    New Member
    Join Date
    Nov 2007
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    New Member
    Join Date
    Nov 2007
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel add st nd rd th to number

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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