+ Reply to Thread
Results 1 to 6 of 6

remove date/time from text string

  1. #1
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2007
    Posts
    2

    remove date/time from text string

    Hello.

    I have a varying text string that includes dates in various formats. I would like to have all dates removed but retain the other non-date text.

    Current:
    012 CHI record debt AUG-12 13-AUG-2012 12:35:12 issue
    "Reverses 012 CH AUG-12 15-AUG-2012" 74923828
    044 GHI insurance 13-AUG-2012 33238922 SEP-12

    Desired:
    012 CHI record debt issue
    "Reverses 012 CH" 74923828
    044 GHI insurance 33238922

    Also, if there is an easy way to parse the 9 digit number that would be great but I can handle it with a formula.

    Thanks!

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: remove date/time from text string

    Hello dan23njguy ,

    Welcome to the Forum!
    I have a few questions:
    1. Is this text all in one cell?
    2. Are these the only date formats to parse?
    3. If there are more can you post them?
    4. Are there multiple cells in column like this one?
    5. Does the revised text go back into the same cell or somewhere else?
    Last edited by Leith Ross; 09-23-2012 at 03:12 PM.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: remove date/time from text string

    Thanks for the welcome! Below are my responses.

    Is this text all in one cell? No, in the example each line is in a different cell (for simplicity, contained in cell A2:A4)
    Are these the only date formats to parse? Yes, it is either in MMM-YY or DD-MMM-YY HH:MM:SS
    If there are more can you post them? These are all examples, there are about 500 rows but the three examples are illustrative of the complete dataset.
    Are there multiple cells in column like this one? No, the are all aligned in column A.
    Does the revised text go back into the same cell or somewhere else? Preferably in the same cell, but wouldn't be the worst thing if the macro placed it in an adjacent cell.

    Thanks for your help!!!!
    Dan

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: remove date/time from text string

    Hello dan23njguy ,

    Sorry for the delay. I had a few errands to run this afternoon. This macro will replace the cell without the dates and times. The macro is written is remove all dates and times in the format you posted from each cell in column "A". The starting cell is "A2". You can change this to whichever cell you want to start with. The macro finds the end of the range for you. The worksheet is assumed to be the ActiveSheet.
    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,569

    Re: remove date/time from text string

    try
    Please Login or Register  to view this content.
    Last edited by jindon; 09-24-2012 at 12:01 AM. Reason: Missed one thing. ##:##:##

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: remove date/time from text string

    Hi dan23njguy,

    Here's my routine:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1