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)
 

Ellen Excell

New Member
Joined
Jan 8, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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?
Rick, the last 2 entries in the cell that contain the word STOP. In this example, STOP appears 5 times. I only need the latest 2 dates/times
STOP@01/06 11:03 (9669)
STOP[08]@01/06 12:03 (96639)
STOP[08]@01/06 14:26
STOP@01/07 09:01 (966)
STOP[08]@01/07 10:29 (966329)
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Ellen Excell

New Member
Joined
Jan 8, 2021
Messages
9
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))
Fluff, this is great but returns an error if STOP only appears once (which on occasion does happen).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,313
Office Version
  1. 365
Platform
  1. Windows
Have you tried the formula from KP117 in post#14?
 

Ellen Excell

New Member
Joined
Jan 8, 2021
Messages
9
Office Version
  1. 365
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))
KP117, this worked perfectly. Can't thank you enough.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,313
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,351
Messages
5,601,127
Members
414,429
Latest member
Bilaal xaka

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