EXCEL Column Formatting issue

By 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.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

This appears to be a FEATURE.

by bart777 In reply to EXCEL Column Formatting i ...

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.

Collapse -

CSV files contain no formatting info

by john In reply to EXCEL Column Formatting i ...

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....

Collapse -

open CSV in WORD PAD

by RMarts In reply to CSV files contain no form ...

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?


Collapse -

BOTH Formats

by RMarts In reply to CSV files contain no form ...

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


Collapse -

It worked fine

by RMarts In reply to BOTH Formats

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

Collapse -

Both Formats... xls and csv

by john In reply to BOTH Formats

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

Related Discussions

Related Forums