Advance Formula Question

[Hyz85]

hi All,
Need your expertise to assist me for below situation :-
how can i extract the datetime (highlighted in red) in one cell?


*** NOTES 01-Nov-2017 08:32:40 Axyz1 Action Type: Manager review
*** Performed by contact: Anonymous Caller, (000)000-0000
Notes:SC- UPSELL
FYNJOIUpgradeEnhancedUpsell1
SUB SIGN UP FAM LITE RM21.2
4MTH CFP98,
PREVIEW WILL ACT 30DAY UPON PUR,ACC MUST BE ACTIVE UPON RBT FULFILLMENT,
NO COMMITMENT,NO RM99 CONV FEES,
1MTH NOTICE UPON CXL,ACC WILL SUSP IF PYMT NOT MAKE 31ST DAYS
SMC#15018555624
DMT#95800TC0528166
CTC#0869540811
Account Id: 94117000,
Decoder Serial Number: 88965TC0528166
*** NOTES 01-Nov-2017 16:40:11 SASMTSYE Action Type: Manager review
1st attempt no call recording 01111
*** NOTES 02-Nov-2017 14:42:22 FMZFAEZA Action Type: Manager review
[!<For Internal Use Only
VT Approved via Call Recording
BILL SMS : 999999999
>!]
*** CASE CLOSE 02-Nov-2017 15:51:22 MHIMOHAM
ACTIVATION DONE


the result should be :
01-Nov-2017 08:32:40
01-Nov-2017 16:40:11
02-Nov-2017 14:42:22
02-Nov-2017 15:51:22

your assistance highly appreciated.
thank you in advance.

 

[Hyz85]

Hi
Just a polite note. In the data I can see

If it's test data or something, fine, but if it's real data you shouldn't use data that can identify a person or organisation here. You never know who might see it.

thank you for the advise.

 

[Hyz85]

Hi, glad it helped. It won't really make any difference but this line:

For i = 1 To Len(s) + 1 Step 1

Should be changed to:

For i = 1 To Len(s)

I was experimenting with ideas and forgot to tidy that line up.

noted. thank you so much

 

[Hyz85]

Best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only. I've edited out a large part of your post 7 quote.

I don't know how many of these texts you have to process, but I thought there might be a faster way than looping right through the strings. The UDF that I came up with is not hugely faster by my testing but it may be worth considering if you do have a lot of data.

Function Get_Dates(s As String) As String
  Static RX As Object
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.Pattern = "(.+?)(\d{2}-[A-Za-z]{3}-\d{4} \d{2}:\d{2}:\d{2})"
  End If
  Get_Dates = Mid(Replace(RX.Replace(Replace(s & ".99-aaa-9999 99:99:99", vbLf, ""), vbLf & "$2"), vbLf & "99-aaa-9999 99:99:99", ""), 2)
End Function

Thank you Peter

 

[Peter_SSs]

thank you for the advise.

Would it be better if anything was removed from, or altered in, that sample data? I am able to edit it if required, just specify exactly what bit(s) need changing.
EDIT: I have since seen your reported post comments and have edited the data. If I have missed anything that should also be removed/disguised, let me know.

Thank you Peter

Glad to contribute.