Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Imported text gets refomatted as date (Excel 2002/SP2)

    When I read tab delimited ASCII data into Excel, if it contains data of the format ##-##-## or ##/##/##, the cells are automatically formatted as "date" which loses the content of the original cell. For example, if I have a text file containing the following 02-01-02, it gets read in as 02/01/2002 and I lose the ability to change it's format back to 02-01-02. Is there a way to keep this automatic date format from being applied?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Imported text gets refomatted as date (Excel 2002/SP2)

    You can write your own routine to import instead of leaving it to excel.

    Record a macro and choose text for the column desired and see what the code is. This will be a good starting point for your macro.

    Steve

  3. #3
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Imported text gets refomatted as date (Excel 2002/SP2)

    Thanks Steve,

    That works. I was hoping for something like a setting so that I can have the users set some parameter and then just drag and drop the files into Excel.

    Thanks again.
    -Joe

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Imported text gets refomatted as date (Excel 2002/SP2)

    Are you using Data|Get External Data|Import Text File to import the file? If so, when I use the settings shown below it imports the fields as text, not dates:
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Sep 2003
    Location
    Camp Springs, Kentucky, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Imported text gets refomatted as date (Excel 2002/SP2)

    Legare,

    By default, mine comes in as "General", but then if the fields follow the format of what "could be" a date, they get formatted as date.

    If I use the method that you describe or even open the file from the "File, Open" menu, I can force the format for that column to text (which works great), but ideally I could turn this automatic format recognition off so that I can either drag and drop the file into Excel or put the cursor over the data file in Windows Explorer and use RMB-Open With, Excel.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Imported text gets refomatted as date (Excel 2002/SP2)

    I don't think that you can do it the ideal way. The best you can do is create a macro that does the Data|Get External Data|Import Text File. Then run that macro from a menu or button.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •