Extract time from date and time text string

[pattem2013]

I have imported a heap of date from a crystal report and in excel i get dates and times as follows:

19/10/2012 3:35:00AM 19/10/2012 4:04:00AM
22/10/2012 3:50:00AM 22/10/2012 4:20:00AM
23/10/2012 3:50:00AM 23/10/2012 4:19:00AM
24/10/2012 3:38:00AM 24/10/2012 3:57:00AM
25/10/2012 3:24:00AM 25/10/2012 3:54:00AM
26/10/2012 3:38:00AM 26/10/2012 4:11:00AM
27/10/2012 5:35:00AM 27/10/2012 6:04:00AM
28/10/2012 6:40:00AM 28/10/2012 7:09:00AM
29/10/2012 3:39:00AM 29/10/2012 4:09:00AM
30/10/2012 4:03:00AM 30/10/2012 4:31:00AM

I need to convert the time from the text cells into Excel time so I can graph the time differences.
Can anyone offer a simple way to do this as i have mountains of data.

Cheers, Matt

[TMS]

Looks like you could start with Text to Columns to separate the dates and times.


Regards, TMS

[pattem2013]

Ok thanks, that separates the times into their own column but they are still text times not numerical.
How to convert the times?

[Debraj Roy]

Try Find & Replace..
Find : "AM"
Replace with : " AM" (with a pre space)

leave the rest work to Excel..

[Fotis1991]

Alternative you could use these formulas

=TIME(MID(A1,11,2);MID(A1,14,2),1)

=TIME(MID(A1,32,2);MID(A1,35,2),1)

Comments?

[pattem2013]

Thanks I found the following elsewhere:
=VALUE(LEFT(B1,LEN(B1)-2)&" "&RIGHT(B1,2))
This seems to work well although I like the very simple answers you have put forward.
Thanks for your help.