help needed converting data
 

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.

For your 10.12.05 guys, using a temporary helper column
enter:
=DATE("20"&RIGHT(A6,2),LEFT(A6,FIND(".",A6)-1),MID(A6,FIND(".",A6)+1,2))

Present Drawbacks (above) are
1) Assumes all years to be 2000+
2) 2 digit months (not singles (1-9))

FWIW,




"MarkP" wrote in message
...
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.

Hi Mark

One way would be
Copy your column of dates to another sheet
Use FindReplace (Ctrl + h) you could search for "th" (without the quotes)
and leave the Replace with field blank.
Repeat for "st", "nd", "rd" etc. and this should resolve those problems
Mark the range of dates, then DataText to ColumnsNextNextthen choose
Date D/M/Y Finish
..

Format the column with whatever date format you prefer, then Copy the column
and Pasteback over your original data.

--
Regards

Roger Govier


"MarkP" wrote in message
...
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.

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

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

Many thanks Jim for your help.

Mark.

Thanks Roger for your help.

Mark.

Dave,

Thanks very much. I will try all the suggestions here.

Mark.