Extract Date and time from the Text Column

[zorro]

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.

[Rick Rothstein]

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)

[zorro]

Thanks, not sure if it is running or not, gives #VALUE!

[Admin]

Have you adjusted the cell A1 in the formula with the actual cell ?

[Excel Fox]

Looks fine in the attachment.

[zorro]

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.

[zorro]

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

[Rick Rothstein]

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...

=IFERROR(0+SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE(L EFT(A1,IFERROR(FIND(" PM",A1),FIND(" AM",A1)))," ",REPT(" ",300)),1500),1500)),",","",2),"")

[zorro]

It works, just that it is picking the time only in AM, even if it is PM

[Rick Rothstein]

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....

=IFERROR(0+SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE(L EFT(A1,IFERROR(FIND(" PM",A1)+2,FIND(" AM",A1)+2))," ",REPT(" ",300)),1500),1500)),",","",2),"")

[zorro]

Perfect, thanks for all your help !!!

[snb]

Or simply


=MID(A1;FIND(":";A1)-15;25)

[jolivanes]

I don't know if you tried snb's formula but if you found that it does not work it is because you need to change the semicolon (;) to a comma(,).
You might also take his counting with a grain of salt. 15 Should be 14 and 25 should be 23.
No offence snb. I know you want people to think for themselves.

[rollis13]

@jolivanes, have you tested with:

Tausif.Shaikh Aug 8, 2016, 11:12:50 PM
or
Tausif.Shaikh Aug 18, 2016, 3:12:50 PM

[jolivanes]

Indeed. I stand corrected.
I guess instead we could use Trim
My apologies snb.

[snb]

@joli

I don't accept, because you shouldn't have apologized in the first place. I always appreciate your contributions very much. %D

[jolivanes]

ok, thanks for the kind words and we'll leave it at that.
Keep on "duimen voor Max Verstappen"

[snb]

Now I don't anymore.... ;)
You remember the agreement of Paris to reduce....

[Admin]

Keep on "duimen voor Max Verstappen"

For Kimi :)

[jolivanes]

@Admin
Kimi is doing better in Singapore.
Could not fing anything for him though.