CODEWITHSUNDEEP
vbaexcel
beeba
beeba

Reputation: 432

Parsing and Concatenaing Dates in Excel From Text

I have the following text data in Notepad. I copied and pasted this data into Excel, and each line occupies one cell. 

    12:07:33 (MLM) TIMESTAMP 8/9/2016  
    11:22:41 (MLM) OUT: 
    11:23:09 (MLM) DENIED: (Licensed number of users already reached. (-4,342:10054 ""))
    11:23:09 (MLM) OUT: 
    11:35:05 (MLM) IN: User 23112
    12:02:50 (MLM) IN:
    12:07:33 (MLM) TIMESTAMP 8/10/2016        
    12:02:50 (MLM) IN: 
    12:02:50 (MLM) IN: 
    12:05:21 (MLM) DENIED:(Licensed number of users already reached. (-4,342))
    12:05:21 (MLM) OUT:   
    12:05:25 (MLM) OUT: 
    12:07:33 (MLM) TIMESTAMP 8/11/2016
    12:11:17 (MLM) OUT:

I need to parse this text through Excel so I can count how often certain terms occur (eg IN, OUT, DENIED etc) and to see how long certain sessions between IN and OUT take.

I can use text to columns to split the terms into different cells. However, I need to concatenate the date information. Every time there is a TIMESTAMP term, all the time columns that follow need to have that date.

So instead of just having [Time], I want the time column to be [Time, Date] where Date is the most recent TIEMSTAMP entry. I want to have something like:

    12:07:33, 8/10/2016 (MLM) TIMESTAMP 8/10/2016        
    12:02:50, 8/10/2016 (MLM) IN: 
    12:02:50, 8/10/2016 (MLM) IN:
    5:07:23, 8/11/2016 (MLM) TIMESTAMP 8/11/2016
    5:11:17, 8/11/2016 (MLM) OUT:

How can I write a function to identify where TIMESTAMP occurs and concatenate it with the time column until the next occurrence of TIMESTAMP? Alternatively, how can I use filters to achieve this?

Upvotes: 0

Views: 93

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

  • Split your data after the Time into two columns (A:B)
  • Insert a new column between A & B
  • With B1 selected, Define a Named Formula:
LastWord *Refers to*    =TRIM(RIGHT(SUBSTITUTE(Sheet3!$C1," ",REPT(" ",99)),99))

Enter this formula:

B1:  =A1+(IF(ISNUMBER(FIND("/",LastWord)),LastWord,INT(OFFSET(B1,-1,0))))

Format as desired. I used hh:mm:ss, m/d/yyyy to make it look like your example, but you could use other formats.

This gives you a "real" date time stamp that you can use as a date in Excel.

However, if your data includes, in LastWord, any non-date words that also contain a slash /, this method will not work, and you will need to use a more sophisticated routine. It also will fail if your Windows Regional date settings are different from the date format in your data.

enter image description here

Upvotes: 1

Related Questions