After Roger wrote his message, you may be able to do the same edit|replace with
the dots and slashes--but I think the mdy (Or dmy or whatever order they're in)
matches your short date setting under control panel.

Dave Peterson wrote:

I'd try to sort those dates into groups based on their format.

Put all the 10.12.05 in a group, same with 10/11/2005.

Maybe add a couple of helper columns.

First helper column would be used to be able to resort the data back to its
original order.

Insert a new column A (or anything you want)
put =row() in the first row
copy it down the column.
select that column
edit|copy
followed by
edit|paste special|values

Now you can always use that column to get it back into the same order. (You can
skip this step if you want.)

=======
Then I'd use another column to categorize the dates.

=IF(ISNUMBER(SEARCH("/",B2)),"slash",
IF(ISNUMBER(SEARCH(".",B2)),"dot","other"))
(all one cell)

Adjust the B2 to match the first cell with the date.

Drag down that column.

Now you can select the dates associated with the dots (later the slashes) and
do:

Data|Text to columns
choose fixed width (delete any lines that excel guessed and don't add any)
Choose mdy (Or dmy or ymd--whatever your dates really are) and click finish.
Now those values are real dates and you can apply the formatting you like.

Do the same with the slashes.

=======
The last portion (25th may 2005) is different.

Depending on what those look like (one isn't enough to know for sure), it could
be as simple as:

Selecting those cells
edit|replace
what: th
with: (leave blank)
replace all.

and format like you want.

1st may 2005
would mean changing st to blank.
2nd may 2005
would mean changing nd to blank.

====
After you're done, you can resort your data by the first helper column and
delete both helper columns.

MarkP wrote:

Hi,

I have a CSV file containing names, addresses, dates etc. Unfortunately the
dates have been entered in different formats eg. 10/11/2005, 25th may 2005
and 10.12.05. Is there a way of automatically changing them to dd/mm/yyyy?
The majority are in the format dd.mm.yy and at the very least I would like
to be able to change them to dd/mm/yyyy. There are about 4000 records so
doing it manually is not an option.

Many thanks.

--

Dave Peterson

--

Dave Peterson