computing
  • 0

Solved Extract Only Dates From a Text In a Single Cell

  • 0

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

Share

1 Answer

  1. I made a couple of changes, that might speed things up,
    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/

    • 0