Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Importing Hyphenated Numbers

    I have a huge CSV file.

    It contains elements, delimited by commas, that are hyphenated.

    Excel wants to interpret these as dates when I import.

    The problem is that it doesn't just format the cells as dates, but it actually changes the underlying data.

    For example, if I have 4-30 in my CSV file, Excel imports this as 40663 (the date code for 4/30/11) and actually changes my data!!! But, if I have 0-21 in my data, it imports this as General and doesn't change the underlying data.

    I am aware of the possibility of doing a search and replace in my CSV to insert a single quote prior to each problem value. I am hoping someone has a better suggestion: if I do this I end up with some values in a column with Custom format and some with General.

    I am willing to entertain fixes that allow Excel to import the 4-30 as 40663, and then fix the 40663 by converting it to a General as 4-30.

    P.S. I cannot set the hyphen as a delimiting character because there are different numbers of elements in each row that suffer from this problem; it works to separate them, but creates a bigger editing problem.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Have you tried specifying the field type as text when you import?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts
    I did, just now.

    I didn't realize that the default was just the leftmost column, and that I had to select all the columns to apply this.

    This is only a partial solution: I have several hundred columns to import, and the hyphenated ones are mixed throughout.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Then your best bet is probably to create a macro that specifies which fields to treat as text.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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