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)
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,985
Office Version
  1. 365
Platform
  1. Windows
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
ABC
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)01/06 11:0301/07 09:01
Sheet4
Cell Formulas
RangeFormula
B2:C2B2=--MID(SUBSTITUTE($A2,"STOP@","|",COLUMNS($B2:B2)),FIND("|",SUBSTITUTE($A2,"STOP@","|",COLUMNS($B2:B2)))+1,11)
 

Ellen Excell

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

Well-known Member
Joined
Dec 30, 2008
Messages
11,985
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 22, 2015
Messages
933
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

What about
VBA Code:
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
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,985
Office Version
  1. 365
Platform
  1. Windows
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
AB
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)01/06 11:03 01/07 09:01
Sheet4
Cell Formulas
RangeFormula
B2B2=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

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

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,313
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,313
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)))
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,714
Office Version
  1. 2010
Platform
  1. Windows
Here is another formula that will work (you can copy it across for more than four cells if you wish)...
Excel Formula:
=IFERROR(0+LEFT(TRIM(MID(SUBSTITUTE($A2,"STOP@",REPT(" ",500)),COLUMNS($B:B)*500,500)),11),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,351
Messages
5,601,130
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