I have a text in a single cell in Excel (exported from a database) which contains random information with timestamps (date and time). I want to extract these time stamps to calculate the time between them. I found ways to extract one of these dates from a cell, but how can I extract all these dates/times to individual cells?
example of text in one cell:
*****************************************************************
13/08/2013 18:14:12 Case 123456. values Changed to: BAU 13/08/2013 18:15:04 We have accepted the service today 13/08/2013 18:59:50 I will review to update records 13/08/2013 19:15:21 The service is ready 13/08/2013 19:17:51 Incident Ended at 13/08/13 17:14:00 GMT 14/08/2013 14:42:04 Closure at 14/08/2013 13:40:00 GMT . Status changed to CLOSED. 14/08/2013 14:42:08 CLOSURE CONFIRMED
*****************************************************************
message edited by kimpep
I reduced it from Three Steps to Two Steps by combining
the last two steps into one.
With your data in cell A1
First:
In cell B1 enter the formula: =SEARCH(“??/??/2013”,$A$1,1)
In cell C1 enter the formula: =SEARCH(“??/??/2013”,$A$1,B$1+20)
Drag cell C1 across to Column I, that is were your dates end.
As you can see, the SEARCH() function allows the use of Wild Card characters,
so we can find the actual beginning without doing any math.
Second:
Now that we have the starting locations of each of the dates
we can combine formulas and save ourselves a step:
In cell B2 enter the formula: (It’s a bit long, so copy and paste.)
=DATE(MID(MID($A$1,B1,19),7,4),MID(MID($A$1,B1,19),4,2),LEFT(MID($A$1,B1,19),2))+TIMEVALUE(RIGHT(MID($A$1,B1,19),8))
Drag cell B2 across to Column I,
this cuts out the date & times from A1 and at the same time
coverts it from TEXT to real Dates & Times.
Makes life a bit easier.
MIKE
http://www.skeptic.com/