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

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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).
 
Upvote 0
Have you tried the formula from KP117 in post#14?
 
Upvote 0
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.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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
Back
Top