1

I am having a problem where I am trying to figure out how I can extract the date and time of the last correspondence in string of text on an excel spread sheet.
Each correspondence within each cell begins with "Additional information" and is then followed by the time stamp, which then ends in "CET" as seen below (Please note I have replaced the body text with Blah, blah, blah):

Example Cell:

Additional information   4/15/15   4:29 PM CET   Ronald Ben  As per phone conversation Blah, blah, blah, blah, blah, blah, blah Thank you
 --------------------------- 
Additional information   4/15/15   3:31 PM CET   Ben I inspected Blah, blah,blah, blah, blah, blah, blah, blah, blah, blah, blah
 --------------------------- 
Additional information   4/13/15   8:02 PM CET   Michael Hi Craig  As per Blah, blah,blah, blah, blah, blah, blah 
--------------------------- 
Additional information   4/13/15   7:19 PM CET   Craig Hello Michael, Blah, blah,blah 
--------------------------- 
Additional information   4/13/15   2:42 PM CET   Blah, blah,blah 
--------------------------- 
Additional information   4/10/15   10:46 PM CET   Mark Hello Craig, Blah, blah,blah

The length of the text string varies from cell to cell, but each are structured exactly the same way.

In the above example the last time stamp should be outputted as 4/10/15 10:46 PM.

I tried to use a RegexExtract function, but it is not working for some reason.

Any help would be appreciated.

Wass
  • 21
  • 4
  • Hey Guys, I realized that I screwed up and this is actually simpler than I thought, but you guys were still a lot of help. The last correspondence is actually at the beginning of the string, but for some reason I was not seeing correctly. – Wass Apr 27 '15 at 23:37

3 Answers3

1

Here is a guide on how to use regex on excel:

https://stackoverflow.com/a/22542835/3393095

And the pattern:

\d+\/\d+\/\d+\s\d+:\d+\s[A|P]M(?!.*(?:\d+\/\d+\/\d+\s\d+:\d+\s[A|P]M))

will do the work if your engine suports lookAhead.

Tested on Regexr.com:

enter image description here

Explanation:

The first part:

\d+\/\d+\/\d+\s\d+:\d+\s[A|P]M

Is basically your Regex for finding the Date.

And the lookAhead clause:

(?!.*(?:\d+\/\d+\/\d+\s\d+:\d+\s[A|P]M))

discards matches that have in front of it another Date match, leaving you with just the last one.

Here is an excellent guide on lookarounds:

http://www.rexegg.com/regex-disambiguation.html

Community
  • 1
  • 1
Rodrigo López
  • 4,039
  • 1
  • 19
  • 26
  • This worked great when I tried it out, but then realized I was looking at my problem incorrectly. I have other applications for where I can use this too. Thanks for the help!! – Wass Apr 27 '15 at 23:32
1

InStrRev will give you the position of the last CET
going back 20 from that point should give you the time

MyStr=range("A1")
Pos=InStrRev(MyStr,"CET")
'You may need to play around with the position values (20 & 12 here)
LastDate=DateValue(Mid(MyStr,Pos-20))
LastTime=TimeValue(Mid(MyStr,Pos-12))
SeanC
  • 15,695
  • 5
  • 45
  • 66
  • I tried this function out, but for some reason I kept getting debug error even when I was adjusting the values to the correct ones. – Wass Apr 27 '15 at 23:34
0

I'm not sure that RegEx is the best solution if you have static text starting each entry as the date/time string can easily be located and peeled out. The CDate function will convert both the date and time portions in a single stroke. 

myDateTime  = CDate(Mid(Range("A1").Value2, 23, 21))

While RegEx may make a nice solution for more dynamic text entries, it just isn't necessary here.