I need to pull the time and date from a cell

[Ellen Excell]

Hi, I'm struggling with finding the right formula to use. I have a cell that contains a great deal of information. I want to pull the date and time after the word STOP in the below example. As you can see the word STOP appears twice and so I would need to pull the date and time after each word STOP. My return should be 01/06 11:03 01/07 10:29. Nothing I have tried seems to do the trick. Any help would be hugely appreciated.

STOP@01/06 11:03 (USER96639) FLAT[08]@01/06 12:03 (USER96639) FLAT[08]@01/06 14:26 (USER96639) STOP@01/07 09:01 (USER96639) FLAT[08]@01/07 10:29 USER(USER96639)

 

[jasonb75]

There are probably a few other ways that you could do this. Result cells need a custom format of mm/yy hh:mm (assuming US date format).

Book1
	A	B	C
2	STOP@01/06 11:03 (USER96639) FLAT[08]@01/06 12:03 (USER96639) FLAT[08]@01/06 14:26 (USER96639) STOP@01/07 09:01 (USER96639) FLAT[08]@01/07 10:29 USER(USER96639)	01/06 11:03	01/07 09:01
Sheet4

Cell Formulas
Range	Formula
B2:C2	B2	=--MID(SUBSTITUTE($A2,"STOP@","|",COLUMNS($B2:B2)),FIND("|",SUBSTITUTE($A2,"STOP@","|",COLUMNS($B2:B2)))+1,11)

 

[Ellen Excell]

This works. Thank you. Due to the limited real estate I have on my output worksheet, is there a way to combine the formulas so the output is in one cell as opposed to 2 separate cells? Thank you for your help

 

[jasonb75]

Does STOP always appear twice in any cell or could it be more times? If so, what is the maximum number of STOPs in any single cell?

 

[mohadin]

What about

Sub test()
    Dim i As Long
    Dim x As String
    Dim m As Object
    Dim cel As Range
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[STOP]+@.\d+\/\d+.\d+\:\d+"
        For Each cel In Range("A2:A" & Cells(Cells.Rows.Count, 1).End(xlUp).Row)
            Set m = .Execute(cel)
            For i = 0 To m.Count - 1
                x = Mid(m(i), 6) & IIf(x = "", "", " ") & x
            Next
            cel.Offset(, 1) = x
            x = ""
        Next
    End With
End Sub

 

[jasonb75]

On the assumption that STOP only ever appears twice in a cell then this with wrapped text will do what you want, for anything more a vba method such as the one suggested by @mohadin would be a preferable choice.

Book1 (version 2).xlsb
	A	B
2	STOP@01/06 11:03 (USER96639) FLAT[08]@01/06 12:03 (USER96639) FLAT[08]@01/06 14:26 (USER96639) STOP@01/07 09:01 (USER96639) FLAT[08]@01/07 10:29 USER(USER96639)	01/06 11:03 01/07 09:01
Sheet4

Cell Formulas
Range	Formula
B2	B2	=MID(SUBSTITUTE($A2,"STOP@","|",1),FIND("|",SUBSTITUTE($A2,"STOP@","|",1))+1,11)&CHAR(10)&MID(SUBSTITUTE($A2,"STOP@","|",2),FIND("|",SUBSTITUTE($A2,"STOP@","|",2))+1,11)

 

[Ellen Excell]

Does STOP always appear twice in any cell or could it be more times? If so, what is the maximum number of STOPs in any single cell?

There is no limit to how many times STOP appears, but so far I've not seen more than 4. My next question would be could I just pull the last 2 incidences of STOP as opposed to all of them?

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://www.myonlinetraininghub.com...which-appears-multiple-times-in-a-single-cell
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

If you have the new LET function, how about

+Fluff v2.xlsm
	A	B
1
2	STOP@01/06 11:03 (USER96639) FLAT[08]@01/06 12:03 (USER96639) FLAT[08]@01/06 14:26 (USER96639) STOP@01/07 09:01 (USER96639) FLAT[08]@01/07 10:29 USER(USER96639) STOP@01/08 21:03 (USER96639) FLAT[08]@01/06 12:03 (USER96639) FLAT[08]@01/06 14:26 (USER96639) STOP@01/08 19:01 (USER96639) FLAT[08]@01/07 10:29 USER(USER96639)	01/08 21:03 01/08 19:01
Main

Cell Formulas
Range	Formula
B2	B2	=LET(Qty,(LEN(A2)-LEN(SUBSTITUTE(A2,"STOP@","")))/5, Sub,SUBSTITUTE(A2,"STOP@","|",SEQUENCE(2,,Qty-1)),TEXTJOIN(CHAR(10),1,MID(Sub,FIND("|",Sub)+1,11)))

 

[Rick Rothstein]

Here is another formula that will work (you can copy it across for more than four cells if you wish)...

=IFERROR(0+LEFT(TRIM(MID(SUBSTITUTE($A2,"STOP@",REPT(" ",500)),COLUMNS($B:B)*500,500)),11),"")