Is there a way to prevent Excel from automatically formatting data
If I open a CSV or Paste in certian types of text data, Excel will reformat
it based on what it thinks the data really is. I would like it to leave my data alone unless I tell it to modify something. For exmaple, if I type in notepad the text 1/23, and copy this and paste it into a new spreadsheet, the result is 23-Jan ( depending on your date settings. ) Why does it assume this value is a date, and how do I tell it to stop? Another example is data that looks like a number that has one or more leading 0's. Excl seems to feel the need to strip the leading 0's to make the value more "numberish" I guess. I don't see why it has to assume that a value is a number just because it contains all digits, it could just as easily belong to a set of serial numbers that occasionally have characters as well. |
Is there a way to prevent Excel from automatically formattingdata
Jason wrote:
If I open a CSV or Paste in certian types of text data, Excel will reformat it based on what it thinks the data really is. I would like it to leave my data alone unless I tell it to modify something. For exmaple, if I type in notepad the text 1/23, and copy this and paste it into a new spreadsheet, the result is 23-Jan ( depending on your date settings. ) Why does it assume this value is a date, and how do I tell it to stop? Another example is data that looks like a number that has one or more leading 0's. Excl seems to feel the need to strip the leading 0's to make the value more "numberish" I guess. I don't see why it has to assume that a value is a number just because it contains all digits, it could just as easily belong to a set of serial numbers that occasionally have characters as well. Hi Jason, Isn't Excel so helpful sometimes? (^: My way of handling external data like this is to, first, always paste as text, and second, either pre-format the relevant receiving columns as Text, or, drop the data once, fix the column types, then paste (as text) again. |
Is there a way to prevent Excel from automatically formatting data
Without going into a discussion of design choices made by MS...
Excel provides you the option of designating any column as TEXT while importing and leaves it alone... Also in Notepad (or in Excel itself) you can enter '1/23 instead of 1/23. "Jason" wrote: If I open a CSV or Paste in certian types of text data, Excel will reformat it based on what it thinks the data really is. I would like it to leave my data alone unless I tell it to modify something. For exmaple, if I type in notepad the text 1/23, and copy this and paste it into a new spreadsheet, the result is 23-Jan ( depending on your date settings. ) Why does it assume this value is a date, and how do I tell it to stop? Another example is data that looks like a number that has one or more leading 0's. Excl seems to feel the need to strip the leading 0's to make the value more "numberish" I guess. I don't see why it has to assume that a value is a number just because it contains all digits, it could just as easily belong to a set of serial numbers that occasionally have characters as well. |
All times are GMT +1. The time now is 01:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com