Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
Many thanks Jim for your help.
Mark. |
#7
|
|||
|
|||
Thanks Roger for your help.
Mark. |
#8
|
|||
|
|||
Dave,
Thanks very much. I will try all the suggestions here. Mark. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I compare 2 sets of data and highlight differences? | Excel Worksheet Functions | |||
How do I import formmail data to a custom excel template? | Excel Worksheet Functions | |||
Help in getting external data needed please | Excel Worksheet Functions | |||
Converting data from a pasted document | Excel Worksheet Functions | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |