Questions

EXCEL Column Formatting issue

Tags:
+
0 Votes
Locked

EXCEL Column Formatting issue

RMarts
when I enter an account 5140-11-30 and create a .CSV file it shows up as a date 11/30/5140 why? This is driving me crasy. When I look at a particular record it shows a single quote in format of the 5140-11-30. I tried to format the entire column to TEXT but same results happen I get a date format displaying 11/30/5140 can some one help me.

Thanks
  • +
    0 Votes
    bart777

    It would appear that since this is normally a date format that the program is assisting you with getting it in the correct format for you. Nice of Uncle Bill to help you like that, isn't it?
    Looks like there are only a couple of things you can do.
    1)put a single quote in front of the account number. (ex> '5140-11-30 )This tells excel to just read what's after the quote.

    2) You could change the format of the account number to use another character like a period or underscore.

    Sorry I don't have a better answer.
    Hope this helps.

    +
    0 Votes
    john

    I assume you are saving to CSV for import into some other program? You can look at the csv file in notepad and probably you'll see that the records come out properly if they looked right in Excel prior to saving in csv format.

    The problem is that you are most likely then trying to load the csv file back into excel, and it has no idea how you formatted them before... it does the same thing it would do if you type that value in, and assumes it is a date until you tell it otherwise.

    The single quote technique will work only as long as you save to an excel format such as .xls if you need the .csv for another application, then save it in BOTH formats....

    +
    0 Votes
    RMarts

    I opened the CSV file in note pad and it looks correct. Now I am using this CSV as an import into a application is this going to cause me an issue?

    Thanks

    +
    0 Votes
    RMarts

    John, when you said to save it BOTH formats what formats are you saying?

    THanks

    +
    0 Votes
    RMarts

    Thank you Bart and John for answering my question. Everything looks good.

    +
    0 Votes
    john

    What I meant is that if you save it in xls and csv formats you can come back to the xls file later and make changes then re-save to csv again to export to your other application

  • +
    0 Votes
    bart777

    It would appear that since this is normally a date format that the program is assisting you with getting it in the correct format for you. Nice of Uncle Bill to help you like that, isn't it?
    Looks like there are only a couple of things you can do.
    1)put a single quote in front of the account number. (ex> '5140-11-30 )This tells excel to just read what's after the quote.

    2) You could change the format of the account number to use another character like a period or underscore.

    Sorry I don't have a better answer.
    Hope this helps.

    +
    0 Votes
    john

    I assume you are saving to CSV for import into some other program? You can look at the csv file in notepad and probably you'll see that the records come out properly if they looked right in Excel prior to saving in csv format.

    The problem is that you are most likely then trying to load the csv file back into excel, and it has no idea how you formatted them before... it does the same thing it would do if you type that value in, and assumes it is a date until you tell it otherwise.

    The single quote technique will work only as long as you save to an excel format such as .xls if you need the .csv for another application, then save it in BOTH formats....

    +
    0 Votes
    RMarts

    I opened the CSV file in note pad and it looks correct. Now I am using this CSV as an import into a application is this going to cause me an issue?

    Thanks

    +
    0 Votes
    RMarts

    John, when you said to save it BOTH formats what formats are you saying?

    THanks

    +
    0 Votes
    RMarts

    Thank you Bart and John for answering my question. Everything looks good.

    +
    0 Votes
    john

    What I meant is that if you save it in xls and csv formats you can come back to the xls file later and make changes then re-save to csv again to export to your other application