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)

 

[sandy666]

raw 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)

raw 01/06 11:03 01/07 09:01

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"raw", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "raw"),
    Filter = Table.SelectRows(Split, each Text.Contains([raw], "STOP")),
    ETBD = Table.TransformColumns(Filter, {{"raw", each Text.BetweenDelimiters(_, "@", " ("), type text}})
in
    ETBD

 

[KP117]

At B2, consider this
=TEXTJOIN(CHAR(10),TRUE,LEFT(TRIM(MID(SUBSTITUTE(A2,"STOP@",REPT(" ",LEN(A2))),1+(ROW($A$1:$A$4)-1)*LEN(A2),LEN(A2))),11))

 

[jasonb75]

@KP117 this is possibly an unlikely scenario but it was my reason for aborting my attempts with a similar formula.
@Fluff I've just tried the same test on your formula and the result is similar, it only appears to pick up the first 2 instances.

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

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN(CHAR(10),TRUE,LEFT(TRIM(MID(SUBSTITUTE(A2,"STOP@",REPT(" ",LEN(A2))),1+(ROW($A$1:$A$4)-1)*LEN(A2),LEN(A2))),11))

 

[KP117]

Please ignore my previous post,#12. Try this instead,
=RIGHT(TEXTJOIN(CHAR(10),TRUE,LEFT(TRIM(MID(SUBSTITUTE(A2,"STOP@",REPT(" ",LEN(A2))),1+(ROW($A$1:$A$10)-1)*LEN(A2),LEN(A2))),11)),24)

 

[Fluff]

@Fluff I've just tried the same test on your formula and the result is similar, it only appears to pick up the first 2 instances.

It should pick-up the latest two, as that's what the OP asked for.

+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
3	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
4	STOP@01/06 11:03 (USER96639) STOP@01/06 12:03 (USER96639) FLAT[08]@01/06 14:26 (USER96639) STOP@01/07 09:01 (USER96639) STOP@01/07 10:29 USER(USER96639)	01/07 09:01 01/07 10:29
Main

Cell Formulas
Range	Formula
B2:B4	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)))

 

[jasonb75]

It should pick-up the latest two, as that's what the OP asked for.

Yeah, just ignore my last post, eyes and brain reading 2 different things

 

[Ellen Excell]

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.

My sincere apologies.

 

[Ellen Excell]

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)))

Unfortunately, I don't have the LET function.

 

[Rick Rothstein]

Unfortunately, I don't have the LET function.

Did you try the formula I posted in Message #10 yet?

Ignore my question above... I missed your revision to the original question that you posted in Message #7. I have a question though. When you said this...

"My next question would be could I just pull the last 2 incidences of STOP as opposed to all of them?"

...did you mean the last two dates in the list no matter what dates they are? Or did you mean you wanted the two latest dates no matter where in the list they appeared?

 

[Fluff]

Ok, another option without using LET

+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
3	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
4	STOP@01/06 11:03 (USER96639) STOP@01/06 12:03 (USER96639) FLAT[08]@01/06 14:26 (USER96639) STOP@01/07 09:01 (USER96639) STOP@01/07 10:29 USER(USER96639)	01/07 09:01 01/07 10:29
Main

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