+ Reply to Thread
Results 1 to 9 of 9

Adding st, nd, rd, th to numbers

  1. 10-10-2015, 01:23 PM #1
    jmcole
    jmcole is offline
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Adding st, nd, rd, th to numbers

    I am using the following formula, but it is not correct. With this formula, it's showing 35rd.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 10-10-2015, 01:32 PM #2
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,481

    Re: Adding st, nd, rd, th to numbers

    Could you post a simple worsheet?
    Quang PT
    Reply With QuoteRegister To Reply
  3. 10-10-2015, 01:33 PM #3
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Adding st, nd, rd, th to numbers

    Have a look at this thread from the 'Tips and Tutorials' section of the forum - it describes how to get ordinal numbers for numbers, dates, etc in various different ways: http://www.excelforum.com/tips-and-t...-or-later.html
    I recommend the Conditional Formatting method, as the numbers stay as numbers so can be used in calculations.
    If the thread is useful, please consider giving the poster there some rep.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.
    Reply With QuoteRegister To Reply
  4. 10-10-2015, 02:33 PM #4
    jmcole
    jmcole is offline
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Re: Adding st, nd, rd, th to numbers

    I tried all of the different conditional formats on there, none of them worked. None of them added anything to the numbers. There is more information in the cell than just a number. The formula I'm using brings up Joe's Birthday 35rd.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 10-10-2015, 02:40 PM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding st, nd, rd, th to numbers

    Quote Originally Posted by jmcole View Post
    I tried all of the different conditional formats on there, none of them worked. None of them added anything to the numbers. There is more information in the cell than just a number. The formula I'm using brings up Joe's Birthday 35rd.

    =C17&DATEDIF(A17,NOW(),"y")&CHOOSE(AND(A17<>{11,12,13})*MIN(4,MOD(A17,10))+1,"st","nd","rd","th")
    Tell us what's in A17 and C17 and tell us what result you expect.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  6. 10-10-2015, 02:56 PM #6
    jmcole
    jmcole is offline
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Re: Adding st, nd, rd, th to numbers

    I trimmed the sheet down to this.

    The results would be the correct st, nd, rd, th behind the numbers.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 10-10-2015, 03:06 PM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding st, nd, rd, th to numbers

    Try this...

    Data Range
    A
    B
    C
    1
    6/11/1980
    Joe's Birthday 35th
    Joe's Birthday
    2
    12/3/1979
    Jennifer's Birthday 35th
    Jennifer's Birthday
    3
    10/28/1997
    Casee's Birthday 17th
    Casee's Birthday
    4
    11/3/2001
    Masee's Birthday 13th
    Masee's Birthday
    5
    2/23/2009
    Jami's Birthday 6th
    Jami's Birthday
    6
    3/31/2001
    Joe & Jennifer's Anniversary 14th
    Joe & Jennifer's Anniversary


    This formula entered in B1 and copied down:

    =C1&DATEDIF(A1,NOW(),"y")&MID("thstndrdth",MIN(9,2*RIGHT(DATEDIF(
    A1,NOW(),"y"))*(MOD(DATEDIF(A1,NOW(),"y")-11,100)>2)+1),2)
    Reply With QuoteRegister To Reply
  8. 10-10-2015, 03:26 PM #8
    jmcole
    jmcole is offline
    Forum Contributor
    Join Date
    01-04-2013
    Location
    Arkansas
    MS-Off Ver
    Microsoft Excel 2016
    Posts
    252

    Re: Adding st, nd, rd, th to numbers

    Quote Originally Posted by Tony Valko View Post
    Try this...

    Data Range
    A
    B
    C
    1
    6/11/1980
    Joe's Birthday 35th
    Joe's Birthday
    2
    12/3/1979
    Jennifer's Birthday 35th
    Jennifer's Birthday
    3
    10/28/1997
    Casee's Birthday 17th
    Casee's Birthday
    4
    11/3/2001
    Masee's Birthday 13th
    Masee's Birthday
    5
    2/23/2009
    Jami's Birthday 6th
    Jami's Birthday
    6
    3/31/2001
    Joe & Jennifer's Anniversary 14th
    Joe & Jennifer's Anniversary


    This formula entered in B1 and copied down:

    =C1&DATEDIF(A1,NOW(),"y")&MID("thstndrdth",MIN(9,2*RIGHT(DATEDIF(
    A1,NOW(),"y"))*(MOD(DATEDIF(A1,NOW(),"y")-11,100)>2)+1),2)
    Awesome! That's it, Thank You Very Much. I have been searching and trying different formulas for hours.
    Reply With QuoteRegister To Reply
  9. 10-10-2015, 04:00 PM #9
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding st, nd, rd, th to numbers

    You're welcome. Thanks for the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Adding Row Numbers
    By dagindi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-27-2015, 10:20 PM
  2. Adding numbers
    By DarKommi in forum Excel General
    Replies: 14
    Last Post: 03-15-2015, 01:58 PM
  3. [SOLVED] Numbers that aren't numbers! + Adding up figures from the same month
    By Ben1985 in forum Excel General
    Replies: 4
    Last Post: 02-11-2015, 11:01 AM
  4. Adding numbers in multiple cells where individual cells contain both numbers and text
    By gamurt in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-08-2014, 10:50 PM
  5. [SOLVED] Adding st, nd, rd, and th to numbers
    By JO505 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-31-2013, 06:46 PM
  6. [SOLVED] Adding numbers to current numbers
    By mk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-16-2005, 07:06 PM
  7. help on adding numbers
    By mavs in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-25-2005, 08:09 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1