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.

 

[FormR]

Hi, welcome to the forum

Is all that text in a single cell?

 

[Hyz85]

yes, all notes/text in a single cell.

 

[gvillepa]

yes, all notes/text in a single cell.

oh dear!

 

[Hyz85]

Hi, welcome to the forum

Is all that text in a single cell?

Yes, all noted/text in a single cell.
the result also should be in a single cell.

your help highly appreciated.
thank you in advance

 

[FormR]

Yes, all noted/text in a single cell.
the result also should be in a single cell.

Hi, it's unlikely you'll be able to get your result using native functions alone, here option you can try that uses a user defined function.

Function GetDates(s As String)
Dim i As Long
For i = 1 To Len(s) + 1 Step 1
    If Mid(s, i, 20) Like "##-???-#### ##:##:##" Then GetDates = GetDates & Chr(10) & Mid(s, i, 20)
Next i
GetDates = Mid(GetDates, 2)
End Function

To use:
1. With your spreadsheet open, press ALT+F11 to open the VBE.
2. Click "Insert" > "Module" on the menu bar.
3. Copy and paste the code above into the blank window on the top right hand side.
4. Press ALT+Q to close the VBE
5. Save your workbook as a macro enabled workbook (xlsm)
6. Use just like any another worksheet function as demonstrated below.

Excel 2013/2016
	A	B
1	*** NOTES [COLOR=#ff0000]01-Nov-2017 08:32:40[/COLOR] 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 [COLOR=#ff0000]01-Nov-2017 16:40:11[/COLOR] SASMTSYE Action Type: Manager review 1st attempt no call recording 01111 *** NOTES [COLOR=#ff0000]02-Nov-2017 14:42:22[/COLOR] FMZFAEZA Action Type: Manager review [!!] *** CASE CLOSE [COLOR=#ff0000]02-Nov-2017 15:51:22[/COLOR] MHIMOHAM ACTIVATION DONE	01-Nov-2017 08:32:40 01-Nov-2017 16:40:11 02-Nov-2017 14:42:22 02-Nov-2017 15:51:22
Sheet1

Cell Formulas
Range	Formula
B1	=GetDates(A1)

 

[Hyz85]

Hi, it's unlikely you'll be able to get your result using native functions alone, here option you can try that uses a user defined function.

Function GetDates(s As String)
Dim i As Long
For i = 1 To Len(s) + 1 Step 1
    If Mid(s, i, 20) Like "##-???-#### ##:##:##" Then GetDates = GetDates & Chr(10) & Mid(s, i, 20)
Next i
GetDates = Mid(GetDates, 2)
End Function

wow!!! Fantastic
thank you so much.
very useful.

really appreciate the help.

 

[FormR]

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.

 

[Johnny C]

Hi
Just a polite note.
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.

 

[Peter_SSs]

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