I need to pull the time and date from a cell

Ellen Excell

New Member
Joined
Jan 8, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
Power Query:
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

KP117

New Member
Joined
Oct 22, 2020
Messages
24
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,986
Office Version
  1. 365
Platform
  1. Windows
@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
AB
2STOP@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
RangeFormula
B2B2=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))
 
Last edited:

KP117

New Member
Joined
Oct 22, 2020
Messages
24
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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)
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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
AB
1
2STOP@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
3STOP@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
4STOP@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
RangeFormula
B2:B4B2=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)))
 

Ellen Excell

New Member
Joined
Jan 8, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 8, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
If you have the new LET function, how about
+Fluff v2.xlsm
AB
1
2STOP@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
RangeFormula
B2B2=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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,714
Office Version
  1. 2010
Platform
  1. Windows
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?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows
Ok, another option without using LET
+Fluff v2.xlsm
AB
1
2STOP@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
3STOP@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
4STOP@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
RangeFormula
B2:B4B2=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))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top