Results 1 to 7 of 7

Thread: How to Split or delete part of a number in excel?

  1. #1
    Join Date
    Jun 2011
    Posts
    109

    How to Split or delete part of a number in excel?

    Please help me to solve my issue. I have a very genuine problem. I got a Row in that there contains 11 digits and I want to delete or move the digits. Not all the digits but the last five so I can make the first six a date. I have tried formulas by myself nut I could not find the result so I thought to consult you experts. All you advices will be appreciated.

  2. #2
    Join Date
    May 2009
    Posts
    511

    Re: How to Split or delete part of a number in excel?

    I can help you with the formula for the date that will delete the last five digits for you automatically after you add that formula in your module. The formula is :

    Code:
    =DATE(MID(A2,5,2),MID(A2,3,2),LEFT(A2,2))
    This above Formula will give you output in English format.

  3. #3
    Join Date
    May 2009
    Posts
    543

    Re: How to Split or delete part of a number in excel?

    If you still haven’t found any help then you can take reference from the below Formula. I have split it in to two parts. Just have look at it and your problem will be solved after you apply it on yours.
    1. For Now:
      =IF(NOW()=DATEVALUE("10/1/92"),TRUE,FALSE)
    2. And For Today:
      =IF(TODAY()=DATEVALUE("10/1/92"),TRUE,FALSE)

  4. #4
    Join Date
    May 2009
    Posts
    529

    Re: How to Split or delete part of a number in excel?

    Copy paste the below Formula in the module of any single cell of your worksheet. It will not only give you date but also delete the last 5 digits and the first 6 digits will be the date.

    Code:
    =YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),AND(MONTH(A2)=MONTH(A1), 
    DAY(A2)<DAY(A1))),1,0)&" years, "&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2) 
    <=MONTH(A1),DAY(A2)<DAY(A1)),11,IF(AND(MONTH(A2)<MONTH(A1),DAY(A2) 
    >=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<DAY(A1)),-1)))&" months, 
    "&A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&" days"

  5. #5
    Join Date
    Apr 2009
    Posts
    569

    Re: How to Split or delete part of a number in excel?

    To return to specific month as per your requirement utilize the below formulas which I have re-edited for you after searching them from over the internet.
    • For example:
    • =DATE(2001,10,14+30) will return November 13, 2001
    • =DATE(2001,10+6,14) will return April 13, 2002

      The code for the same will be as follows:
    • =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

    • =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

  6. #6
    Join Date
    May 2009
    Posts
    637

    Re: How to Split or delete part of a number in excel?

    I have some specific example from which you can take guidance and use it as per your requirement. I have easily set for you so that you will easy to understand and use them.

    1. Number_ A10=22122001
    2. Formula_ DATEVALUE(LEFT(A9,2)&"/"&MID(A9,3,2)&"/"&RIGHT(A9,4))
    3. Output_ 22/12/2001


    1. Number_ A11=September
    2. Formula_ CHOOSE(MATCH(A11,{"January";"February";"March";"April";
      "May";"June";"July";"August";"September";"October";
      "November";"December"},0),1,2,3,4,5,6,7,8,9,10,11,12)
      ie; what month is September
    3. Output_9


    1. Number_ A12=22/05/01
    2. Formula_ DATEDIF(A13,A12,"M") ie; how many months between A12 & A13
    3. Output_16

  7. #7
    Join Date
    May 2009
    Posts
    539

    Re: How to Split or delete part of a number in excel?

    I have more examples for you. They are as below:

    • Number_ A14=15/12/01

    Formula_ EOMONTH(A14,3) ie; the last day of the month 3 months from A14
    Output_31/03/2002

    • Number_ A15=18/02/01

    Formula_ EDATE(A15,-1) ie; the date 1 month before A15
    Output_18/01/2001

Similar Threads

  1. Dell Studio Slim 540 expansion slot cover and its part number
    By Natalia.123 in forum Hardware Peripherals
    Replies: 2
    Last Post: 13-05-2012, 10:18 AM
  2. Part number not accepted for windows 7 upgrade
    By Xan Mathew in forum Hardware Peripherals
    Replies: 3
    Last Post: 08-04-2010, 03:05 AM
  3. VOB editor needed to delete part of a file
    By Arif15 in forum Windows Software
    Replies: 3
    Last Post: 13-06-2009, 05:37 PM
  4. How to display only part of a decimal number
    By Gauresh in forum Software Development
    Replies: 2
    Last Post: 31-01-2009, 09:20 AM
  5. Help creating 2 part page number
    By dfeder in forum Windows Software
    Replies: 3
    Last Post: 12-11-2008, 12:14 PM

Tags for this Thread

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
  •  
Page generated in 1,711,652,186.99649 seconds with 17 queries