Modifying and separating Date & Time from single cell

Hello,

I have a string of the following text in one column:
May 2 2013 08:54:46:827AM

How it is possible to change it to the following:
2.05.2013 8:54:23

Date and time in the separate columns.
8:54:23 - military time

Maybe something like that could do the trick?
=REPLACE(REPLACE(A1,FIND(":",A1)-2,9,MID(A1,2,FIND(" ",A1,2)-1)),1,FIND(" ",A1,2),"")+LEFT(RIGHT(A1,13),8)+0

(Don't have enough knowledge to modify that formula)
file attached
excel issue.xls

Try setting the numberformat of the cell to .... custom ->> d.mmm.yyyy h:mm:ss

If it displays correctly you are dealing with a numerical date otherwise it is a string..
Once we know the answer to the above question we will know how best to proceed..

I couldn't open your file...

The formula to pull the date from B2 could be

=DATE(VALUE(TRIM(MID($B2,FIND(" ",$B2,FIND(" ",B2,1)+1)+1,FIND(" ",$B2,FIND(" ",$B2,FIND(" ",B2,1)+1)+1)-FIND(" ",$B2,FIND(" ",B2,1)+1)))),MATCH(LEFT(B2,FIND(" ",B2,1)-1),MonthList,0),VALUE(TRIM(MID($B2,FIND(" ",B2,1)+1,FIND(" ",$B2,FIND(" ",B2,1)+1)-FIND(" ",B2,1)))))

Where "MonthList" is a list of the 12 months written as they appear in the main data

The time is

=TIME(LEFT(RIGHT(B2,LEN(B2)-FIND("~",SUBSTITUTE(B2," ","~",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))),2),MID(RIGHT(B2,LEN(B2)-FIND("~",SUBSTITUTE(B2," ","~",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))),4,2),MID(RIGHT(B2,LEN(B2)-FIND("~",SUBSTITUTE(B2," ","~",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))),7,2))

This workbook might suit your needs, the formulae are a tad shorter.

[EDIT]
@ Andy
Does your time formula handle AM/PM?

Ahhh ... good point Marcol - NO it doesn't ...

Hi happyc

See the attached for another version. The times in column C are assuming the times in column A are always 8 digits long eg: May 1 2013 11:30:38:170PM

My apologies I missed the 12AM/12PM scenarios ...

To keep things simple I would insert a helper column to return the hour from the TIMEVALUE() formula, Column D in this workbook.
This can be hidden with the +/- grouping button.

I have included a column to expand the formula if you dont want the helper, and similarly for the date without a lookup table.

Note the change in the formula in C2, it now handles either of these TimeStamp format possibilities
Dec 2 2013 08:54:23:903AM
December 2 2013 08:54:23:903AM

[EDIT]
Go with Kevin, he's thinking ... I'm not!