Change text date to normal date

Hi,
My name is Marc and I hope somebody can help me creating a VBA for my problem.
I have included a file with the problem. See the Sheet Movies_seen.

So in column “I” (Created) each cell has a date and time as a text string, it looks like this:
Sun Aug 24 04:30:24 2014
I want to replace this text date and time, into a normal date. This has to be in the same cell (sheet and column).

For example: Sun Aug 24 04:30:24 2014 will look like: 24-8-2014 (first the day, then month and last the year, separated by a “-“.

Only the date is of importance to me, the rest of the text and time of the cell can be ignored/deleted. The length of the column, in this example to row 89, is a variable.

Hope my explanation is clear, if not feel free to contact me so I can explain some more.
Thanks in advance for all your help.
Marc

TimeValue and DateValue and Format functions.

Porucha verku,
Thanks for your response but it's not entirely clear to me how I should use the functions: TimeValue and DateValue and Format in this case.
Could you elaborate somewhat on this?
Thanks!
Marc

Originally Posted by TMS

@gfranco: as far as I can see, a) that's not an array formula and b) it will always return 4 as the day of the month

You're right. I believe that the translation is not as faithful as the one automatically produced by excel.
That's why I attached the spreadsheet.
My excel is in Portuguese (Brazil). That's why I end up sending the worksheet instead of writing the formulas in the "codes".
Do you understand my difficulty?

Originally Posted by TMS

Try:

Please Login or Register  to view this content.

In non-english systems it fails because of others naming of months, month must be an integer.
I propose, very similar:

Please Login or Register  to view this content.

Function 'Trim' habitually ...

Unfortunately, english "January" "in word" does not pass, as number yes - mp4 in zip (e.g. with mpc-hc player)
So, "January and his eleven friends" no pasaran ... ...

an array fórmula in my excel

cramnij,
Try and see how it goes.

Please Login or Register  to view this content.

Or try:

=--(TRIM(MID(I2;9;2))&MID(I2;5;3)&RIGHT(I2;5))

Thanks porucha vevrku,
With some minor tweaks it works exactly as it supposed to do.
To all the others also many thanks for your work and thoughts about this conundrum of mine.
Marc

Thanks, but TMS's and jindon's solutions are also worth trying for you.
TMS's for simplicity (small code length) and efficiency (but here only for english system configuration),
and
jindon's for "technical sophistication/advancement" of the code using regular expressions and effective although/though rarely used function ... real "Made in Japan" ...
Use this because you have the opportunity to learn something new.