How to extract date from text string

[Gunjiddash]

Im working with a dataset that has work hours in the following format (11:00 am - 8:00 pm). How can i extract the start time and end time into 2 separate columns as per the photo below? I know you can use a =MID(A2, SEARCH function but don't know how to set the parameters. Please help!

 

[Gunjiddash]

=mid(F2,find("-",F2)-9,8) for the start time
=mid(F2,find("-",F2)+1,8) for the end time

 

[Fluff]

You could also just use

=LEFT(F2,8)
=RIGHT(F2,8)

If you want to get rid of any extra spaces you could wrap them both in Trim

 

[Peter_SSs]

You could also Select E2:Ex then use Text to Columns (on the Data ribbon tab) -> Fixed width -> Next -> Adjust dividers (double-click &/or drag) like this

-> Next -> Destination: F2 -> Click in the middle column at the bottom & choose 'Do not import column'

Finish

Result:

20 11 02.xlsm
	E	F	G
2	11:00 am - 8:00 pm	11:00 AM	8:00 PM
3	11:00 am - 8:00 pm	11:00 AM	8:00 PM
4	11:00 am - 8:00 pm	11:00 AM	8:00 PM
Split Times

 

[mohadin]

And
Just if you like to use a VBA

Sub test()
    Dim a
    Dim i
    a = Cells(2, 1).Offset(, 4).Resize(Cells(Rows.Count, 5).End(xlUp).Row - 1)
    ReDim b(1 To UBound(a))
    For i = 1 To UBound(a)
        b(i) = Split(a(i, 1), "-")
    Next
    b = Application.Transpose(Application.Transpose(b))
    Cells(2, 1).Offset(, 5).Resize(UBound(b), 2) = b
End Sub

 

[Peter_SSs]

And
Just if you like to use a VBA

Sub Test2()
  Range("E2", Range("E" & Rows.Count).End(xlUp)).TextToColumns Range("F2"), xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(8, 9), Array(11, 1))
End Sub