Recognize date using text formulas

[jrtaylor]

;I have a large, imported worksheet which has dates in this format:

Please Login or Register  to view this content.

This is all in one cell.

I need help converting this to an excel-recognized date and time (i.e. 4/01/09 4:00 PM)

I've attached a sample spreadsheet. I've tried using LEN but my brain can't do the logical work required!

Thanks!

[tigeravatar]

This:
=TEXT((LEFT(B6,FIND(" ",B6,FIND(" ",B6)+1)-1)&", "&MID(B6,LEN(B6)-7,4))+MID(B6,LEN(B6)-16,8),"m/dd/yy hh:mm AM/PM")

Or just (this will require you format the cell to m/dd/yy hh:mm AM/PM):
=(LEFT(B6,FIND(" ",B6,FIND(" ",B6)+1)-1)&", "&MID(B6,LEN(B6)-7,4))+MID(B6,LEN(B6)-16,8)

[jrtaylor]

Hey thanks so much! Perfect!

[Haseeb Avarakkan]

Try also,

=SUBSTITUTE(SUBSTITUTE(B6," ","," &LEFT(RIGHT(B6,9),6),2),RIGHT(B6,8),"")+0

format as desired.