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.
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.
Thanks, not sure if it is running or not, gives #VALUE!
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)
Looks fine in the attachment.
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
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.
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
Well, that changes everything. Assuming your time values always have either an AM or PM designation, give this formula a try...Originally Posted by zorro
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.
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),"")
Posting Permissions
Reply With Quote
Bookmarks