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. 


0
8/25/2005 9:05:00 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
476 Views

Similar Articles

[PageSpeed] 33

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" <fedupofspam@fedupofspam.com> wrote in message 
news:eBAuUQVqFHA.712@tk2msftngp13.phx.gbl...
> 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.
> 


0
jmay (696)
8/25/2005 11:27:18 AM
Hi Mark

One way would be
Copy your column of dates to another sheet
Use Find>Replace (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 Data>Text to Columns>Next>Next>then 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" <fedupofspam@fedupofspam.com> wrote in message 
news:eBAuUQVqFHA.712@tk2msftngp13.phx.gbl...
> 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.
> 


0
roger5293 (1125)
8/25/2005 12:02:49 PM
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
0
petersod (12004)
8/25/2005 12:29:07 PM
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
0
petersod (12004)
8/25/2005 12:44:44 PM
Many thanks Jim for your help.

Mark. 


0
8/25/2005 10:01:50 PM
Thanks Roger for your help.

Mark. 


0
8/25/2005 10:02:13 PM
Dave,

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

Mark. 


0
8/25/2005 10:03:01 PM
Reply:

Similar Artilces:

error in importing data
Sorry the error is 0x8004032d ...

Re: Extracting Exchange User Data From AD 02-24-10
If you do not like scripting, you can try GAL Exporter or Fast User Manager & Reports from IMIBO - http://www.imibo.com > > "Ringholz, Blake" <bringholz@nospam.com> wrote in message > news:76AEFC2F-85A7-4666-8262-27FB0737D09A@microsoft.com... >> Hello All - >> >> I need to get an Excel Spreadsheet that lists everyone first name, last >> name, email address, job title, etc pulled from Active Directory. Is >> there an easy way to do this? >> >> Thanks, >> Blake > > > > > ...

Macro help #12
Hi, I am working on a macro in Excel 2003 Pro, and everytime I go to sort a column of data. There are about 10 rows of data in about 200+ columns. I can only get the macro to sort the column that I edited it with. How can I create the macro, so each time I click on the first row of each column, click the macro button I will assign to the toolbar, then that row will be sorted, and do this for each column? Any help at all would be greatly appreciated, Jeff Garrett (user_jeff@hotmail.com) Hi Jeff please post your current code and then we can suggest changes to it. Cheers JulieD "...

How do I prevent "Help" from opening when I open Excel?
Whenever I open Excel 2003 the Help feature opens on the right. How do I pevent this? I only want Excel to open with a new sheet. -- Lee P Lee Help or TaskPane. For Help, you should be able to close it using the "x" and next time open Excel should not display. If TaskPane, go to Tools>Options>View and uncheck "Startup Task Pane" Gord Dibben Excel MVP On Mon, 12 Dec 2005 13:05:29 -0800, "Lee P" <LeeP@discussions.microsoft.com> wrote: >Whenever I open Excel 2003 the Help feature opens on the right. >How do I pevent this? I only want Exce...

Excel 2007 Need to permanently change Normal settings for gridlines
I am using Excel 2007 for the first time and find the gridlines delineating the cells are so faint as to be nearly indistinguishable. I can go into the cell formatting and modify the normal style, and it is just the way I want it. But I can't find a way to make Excel remember this and treat it as the new definition of the Normal style. I don't want to have to redefine Normal every time I open a new spreadsheet. Someone please help! Regards Leonard Priestley The changes you describe are changing the Border color and NOT gridlines. Go into Excel Options and cl...

Converting XLS file to QIF or to OFX
How do I safely and securely convert an excel file (xls) to a QIF or OFX file? "dreamchaser" wrote: > How do I safely and securely convert an excel file (xls) to a QIF or OFX file? In Excel, save the file to CSV and ustilise iCreateOFX Basic from: http://icreateofx.co.uk/Convert-CSV-to-OFX to convert the saved CSV file to OFX. ...

Doing Analysis from large amounts of DATA
Hi all, I have an attachment that's apart of this message & within it I have 3 tabs 1) Revenue 2) Expenses & 3) Net Position. I would like to do some Analysis on the above three thou a way where I can manipulate the Data. How would I go about doing this? I've tried a Pivot Table thou it didn�t work out in the sense that it was messy & just failed in calculating stuff, not sure if I did it right thou.. Would really appreciate all the help as I actually have about 20 of these sheets which from that fall into 4 groups being 5 sheets per group. I hope this all makes sense, wo...

lost data when opening excel workbooks ; text import wizard popup
When opening many of my excel files ,which all have the same modification date, I come across the text import wizard which states that my text in these files is 'delimited'. All of the files ,including a few word doc.s have had their data changed to show all " y " with two dots above the letter for as far as the eye can see. No import or export has been done with the files and no modifications were done on that date, as far as I know. Is this a corruption problem or is their some 'fix' that I am overlooking. Thanks for any ideas. ...

KB930879 needs clarification
Hi all, I just check the MS KB930879 reviewed on 2/22/2007. I am so confused now. Please look at the section what to do after you run exchange tool ******************************** What to do after you run the Exchange tool Install updates After you finish updating all Exchange servers in your environment, install the following updates on the Exchange servers: 931836 (http://support.microsoft.com/kb/931836/) February 2007 cumulative time zone update for Microsoft Windows operating systems 926666 (http://support.microsoft.com/kb/926666/) Update for daylight saving time changes in 200...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

Do you need Access to use an Access Database for data entry only?
We have an Access database completed. Now we will use it for reading as well as for data entry. Do we need to install Access for every workstation needing the database for data entry or reading? No you don't necessarily need Access on every computer. You CAN use the Access Runtime and if it is in Access 2007 you can use it for free. If you are using another version then you would need to purchase the developer's edition to be able to legally distribute the associated Access runtime. If you do have 2007 and want to use the runtime, make sure your Full version of Access doe...

Date Range Formula Question
Hello, I'm having trouble with a formula and I'm hoping someone can help. :confused: Sample Data Includes the following: Pay Period Start Pay Period End Pay Period # 12/16/01 12/29/01 26 12/30/01 01/12/02 25 01/13/01 01/26/02 24 01/27/02 02/09/02 23 02/10/02 02/23/02 22 The pay periods continue until there are 26 pay periods for the entire year....

Importing data into Outlook #2
Is there a process or program with which I can transfer my ACT! data into Outlook contacts? Thanks! ...

How to convert privatekey(string) to XML
Hi all, I need to convert privatekey toXML format. I read privatekey from file. Here is how: -------------------------------- Dim myStream As System.IO.StringWriter = New System.IO.StringWriter Dim testfile As String = "c:\RSAprivate.txt" Dim objStreamReader As System.IO.StreamReader objStreamReader = System.IO.File.OpenText(testfile) Dim readfile As String = objStreamReader.ReadToEnd() TextBox1.Text = readfile objStreamReader.Close() -------------------------------- and the privatekey looks like this: -----BEGIN RSA PRIV...

HELP!!! 08-30-03
please elp me. my mom is a medical transcriber and she has a dictionary she uses through windows 98. and her comp has no memory, so we need to update her operating system inoder to put more memory on there. she needs her dictionary and i was wandering how to access it and if possible how to save it on to a cd. thank you for ur time Dear jason you have to have a dictornary corect i have one i could email you if you like just email me back i mean i use this dictornary really well and it works for 95-Xp so if you want give me a hollar ok peace out danny "jason" <jasonr_206@hotm...

Exchange server recovery advice needed
Folks We have been hit by a relatively big disk crash and I have to rebuild our exchange 2003 (SP1) server Fortunately I have a fully working backup but I still need to "replay" some LOG files as we only do weekly "full" snapshot. So to cut a long story short I have recovered and restarted the server as it was one week ago and everything seems fine EXCEPT for the messages (and other items) created since last backup. I DO have the log file for those days... but how do I "replay" them ? What is my best course of action ? At the moment I plan to create a separat...

help with installation isue
Hope this is correct NG When I try to run `Karens replicator` (for external backup) My PC automatically runs an installation for publisher 2003 and then gives error message........... Istallation error require file SKU019.CAB then asks for original CD to be inserted. Publisher still works ok I can`t see what the relationship between the 2 are, how do I stop this trying to install? thanks -- Rent a villa in Turkey http://www.freewebs.com/turkeyrent/ I would suggest aksing the folks over at karenware.com. http://www.karenware.com/powertools/troubl...

HR Help menu
The Help menu is not available in the Employee file screen. Also there is not at this time any french version help menu available with HR. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/de...

How do I show a data table with legend keys in a line chart?
I am having trouble inserting a data table with legend keys into a line chart that contains a horizontal target line. Can anyone help me? Hi, I tried it in both 2007 and 2003 and had no problems. We need more infomation, can you post a sample of the chart somewhere. The only issue I have is that the target line is displayed on the data table, which I would prefer that it not be. -- Thanks, Shane Devenshire "excelbanker" wrote: > I am having trouble inserting a data table with legend keys into a line chart > that contains a horizontal target line. Can anyone he...

need to make a formula that would add a field value to current dat
I have made a form in which I input different values. On of the values is (How Many Days). Now I need to a assign a default value, or expression (not sure which way to go about this) that will take the date value for (Date) and add the value (How Many Days) I figured that the formula should read =sum([Date]+[How Many Days]) But that is not giving me any results, thanx for your help in advance =DateDiff("d", Date(), [How Many Days]) -- Wayne Manchester, England. "J Man" wrote: > I have made a form in which I input different values. On of the values is ...

Output the list of frequent data
Dear all, Here comes two problems. Problem 1: I have a list of strings (say, in the column A1:A100). How can I find the "mode" (i.e. the string appearing most frequently in the list? For example, if the list is APPLE APPLE BANANA APPLE CREAM CREAM BANANA CREAM APPLE DONUT then I want the result is APPLE. It seems that the MODE function does not suppot data type other than numbers. Is there any canned UDF for it? Problem 2: Following Problem 1, I want to generate a list of the 3 most frequent data in the list. If the list is the one in the example, I want to list to be...

Barcodes Code
I am trying to print a barcode in the 128 code. Though it seems the public domain/GPL ones of the truetype do not print a scanable barcode. Is there any suggestions... Or if anyone can point me into the right direction on how to do this or point me in direction of one that will work with access or excel 2000 or 2003. Much appreciated. Or help me figure out WHY it isnt producing a readable one in 128.. Hi Lester, Perhaps the problem is with the resolution on your printer. Try printing the barcode larger. Perhaps even turn off print to fit under print, page setup. --- HTH, David McRi...

Data Validation #47
kshave multiple identacle sheets in a workbook(31 sheets). I am trying to allow users to only enter whole numbers in some of these cells. I know I can do it through Data /Validation/Allow - but, I want to do it on all 31 sheets at once. When I select all sheets - Validation is greyed out. Should I be using a different method? Thanks, Tom Apply the data validation on Sheet1 Copy the cell(s) in which you applied data validation Select Sheet2 Scroll to see sheet31 Hold the Shift key, and click on Sheet31 tab Select the cell where you want to paste the data validation Choose Edit>Paste Cl...

Modifing data to show up better in chart.
My Y1 axis is from 0-250 my Y2 axis is from 0-9000 When graphing percentages the line shows up at the very bottom since 99% < 1 is there a way I can multiply these percentages by 100 to show them as 9900% = 99 Normally you would just make then a 2nd axis but as you can see I already have a second axis of 0-9000 In article <A9E6F75B-7307-4291-BC7D-729F9DF88D50@microsoft.com>, MikePunko@discussions.microsoft.com says... > My Y1 axis is from 0-250 my Y2 axis is from 0-9000 > When graphing percentages the line shows up at the very bottom since 99% < 1 > is there a way I...

Access Outlook Add-in for Data Collection & Publishing won't load
I am trying to utilize the Collect Data via e-mail function and keep receiving the following error: "The Microsoft Office Access Outlook Add-in is disabled in Microsoft Office Outlook 2007. To collect data by using e-mail messages in Microsoft Access 2007, verify that this add-in is installed and enabled from within Office Outlook 2007." I have tried to remediate via the Trust Center/Add-in area in Outlook and don't see an add-in for Access (even though the error message says it is "disabled" it is nowhere to be found). I have tried to add it (I believe it is...