Extract Date and Time from a string

[srrboarder]

Hello,
I'm working with a data export that is throwing a time stamp entry into 1 cell. I need to break out the date and time into separate cells so that excel will recognize both as a date and a time. Can someone help build a formula that will separate the necessary data?

Assume my data starts in A1 (there will not be a set number of entries but all entries will be within column A).

Data in A1 looks as follows: Friday, June 24, 2011 at 4:04pm

I need "June 24, 2011" to go into its own column

I need "4:04 PM" to go into its own column --> Also, please note that the time stamp does not put a space between the numeric time and the "AM/PM" so the formula would need to place that space there instead.

Please keep in mind that the dates are for the entire year (so multiple months are represented and times may also contain 4 numeric digits instead of just 3 (ex.12:00).

Thanks for any help you are able to provide.

 

[rjkincaid]

D'oh!

My formula works by finding the "at" between the year and the time......but of course Saturday is the one day of the week which also contains "at".....you can amend formula slightly to search for "at " (with a space), i.e.

=REPLACE(LEFT(A1,FIND("at ",A1)-2),1,FIND(",",A1)+1,"")+0

or, use FIND like this:
=FIND("at",A1,4)
By adding the start position of 4, you'll bypass the first 3 characters in the search but still receive the correct position of " at "

 

[srrboarder]

Oh good grief! Thanks Barry. I don't know how I missed that one! Thanks again for the help!