+ Reply to Thread
Results 1 to 6 of 6

Formula or macro to remove characters between date

  1. #1
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Formula or macro to remove characters between date

    Hi. I would like to have a formula or macro to remove characters or spaces beyween dates.
    So 14.07.2014 will be 140714. Year always only 2digits.
    No matter what the input is. Could be these. 14/07/14 .14-07-2014, 14.07.14. What ever always. Result with no spaces or charaters and year always only 2digits.
    Please have a look.
    Sincerely
    Abjac

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Formula or macro to remove characters between date

    In A1 Cell

    14.07.2014


    For getting the Text Output

    =TEXT(SUBSTITUTE(A1,".","-"),"DDMMYY")



    For getting the Real Value, But preceding zero will get removed

    =VALUE(TEXT(SUBSTITUTE(A1,".","-"),"DDMMYY"))


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Formula or macro to remove characters between date

    Hi Sixthsense. Thanks for your reply. I get a formula error. Maybe its because I use excel 2003. Could you have a look at it.

    Thanks in advance

    Sincerely
    Abjac

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Formula or macro to remove characters between date

    It seems that your regional Date/Time Short Date setting set to MM-DD-YYYY, just change it to MM-DD-YYYY

  5. #5
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Formula or macro to remove characters between date

    Hi.

    Changed the formula to this =TEXT(SUBSTITUTE(A1;".";"-");"DDMMYY")
    But still wrong result for it 1407YY
    So need to change some with the year also.
    Have a look thanks

    Abjac

  6. #6
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Formula or macro to remove characters between date

    HI I think i solved it. You was right, i was or my computer had other regional settings, so for me it was not YY but ลล i should put in your formula.
    So Sixthsense thanks allot for you help. Sorry i could not get this, but its working for sure now, so your help was great.

    Thanks allot

    Abjac

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to remove certain characters end of a cell value
    By mergleh in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-09-2014, 05:19 PM
  2. remove spaces and characters in hyperlink - macro
    By missy22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-05-2013, 09:23 AM
  3. Formula to remove unwanted characters
    By adam2308 in forum Excel General
    Replies: 4
    Last Post: 01-17-2013, 04:38 AM
  4. [SOLVED] Macro to remove multiple characters in column?
    By emil9216 in forum Excel General
    Replies: 3
    Last Post: 07-31-2012, 09:29 AM
  5. Macro to remove any numeric characters from just the last value
    By Renfield in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-09-2010, 12:03 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1