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

Thread: Extract Date and time from the Text Column

  1. #1
    Junior Member
    Join Date
    Aug 2016
    Posts
    12
    Rep Power
    0

    Extract Date and time from the Text Column

    This is how my text looks like.
    Unable to identify the source system. The user account seems to be a service account for Rancid which is a config backup software. Tausif.Shaikh Aug 8, 2016, 3:12:50 PM
    I need to extract just the date and time from this text, date and time is always at the end.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Give this formula a try (Custom Format the cell to display the date-time format you want)...

    =0+SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",300)),1500),1500)),",","",2)
    Last edited by Rick Rothstein; 08-17-2016 at 09:06 PM.

  3. #3
    Junior Member
    Join Date
    Aug 2016
    Posts
    12
    Rep Power
    0
    Thanks, not sure if it is running or not, gives #VALUE!

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Have you adjusted the cell A1 in the formula with the actual cell ?
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Looks fine in the attachment.
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Junior Member
    Join Date
    Aug 2016
    Posts
    12
    Rep Power
    0
    Yes did adjusted the right cell, anyways the sheet that you have sent works perfectly fine, so copied over the data onto it and moved the results back to the original sheet, thanks for all your help.

  7. #7
    Junior Member
    Join Date
    Aug 2016
    Posts
    12
    Rep Power
    0
    Just realized that date and time is not always at the end, it's anywhere in the column, please help to extract it from the below text.

    Current count 119. Keeping under monitoring. Admin.Tausif Aug 9, 2016, 6:48:14 PM "This offense was closed with reason: Activity Stopped. Notes: Activity has stopped now and no more failed login attempts were observed

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by zorro View Post
    Just realized that date and time is not always at the end...
    Well, that changes everything. Assuming your time values always have either an AM or PM designation, give this formula a try...
    Code:
    =IFERROR(0+SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE(LEFT(A1,IFERROR(FIND(" PM",A1),FIND(" AM",A1)))," ",REPT(" ",300)),1500),1500)),",","",2),"")
    Last edited by Rick Rothstein; 08-18-2016 at 07:44 PM.

  9. #9
    Junior Member
    Join Date
    Aug 2016
    Posts
    12
    Rep Power
    0
    It works, just that it is picking the time only in AM, even if it is PM

  10. #10
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by zorro View Post
    It works, just that it is picking the time only in AM, even if it is PM
    Sorry, I forgot to add 2 in order to retain the AM/PM designation. Here is the revised formula....
    Code:
    =IFERROR(0+SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE(LEFT(A1,IFERROR(FIND(" PM",A1)+2,FIND(" AM",A1)+2))," ",REPT(" ",300)),1500),1500)),",","",2),"")

Similar Threads

  1. Extract Time from a Date&time cell
    By Feebles in forum Excel Help
    Replies: 4
    Last Post: 11-09-2015, 06:24 AM
  2. Extract Only Numbers & TEXT From Text String
    By mahmoud-lee in forum Excel Help
    Replies: 9
    Last Post: 11-02-2013, 02:49 PM
  3. How To Change Date Based On Time Using Formula
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 08-03-2013, 11:03 PM
  4. Date & Time Formats in Different Languages
    By Haseeb A in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 06-10-2012, 02:57 PM
  5. Displayin Date/Time in "original" time zone
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 04-21-2012, 02:02 AM

Posting Permissions

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