4 ms·
- Excel constantly wants to change cell contents, in the name of improving formatting. For example, paste a table with something that excel thinks are dates, it
by ant6n 6y ago
- Excel constantly wants to change cell contents, in the name of improving formatting. For example, paste a table with something that excel thinks are dates, it will convert it to dates, and show the data in some format. Formatting it back to text will give you the #days since epoch instead of the original text.
- Excel hates text cells containing numbers. It whines about it all the time and eagerly changes the data to what it thinks it should be.
- Excel doesnt get it if a sheet contains a data table with consistent formatting. Just recognize it and store it internally as a small Infile DB. Often, an Altertx table will blow up 100-fold when exported to Excel.
- linuxftw 6y agoI haven't tried it on Excel specifically, but most programs support ctrl+shift+v to clear formatting.
- ant6n 6y agoIt clears formatting in incoming data, but doesn't prevent Excel from applying its own formatting afterwards.
- OldHand2018 6y ago> Excel constantly wants to change cell contents, in the name of improving formatting. For example, paste a table with something that excel thinks are dates, it will convert it to dates, and show the data in some format. Formatting it back to text will give you the #days since epoch instead of the original text. This is definitely annoying behavior, but you do know that if you format the cell as text prior to pasting the data in it will keep it as text, right?
- marcosdumay 6y agoSome times yes, other times Excel will replace the format you set with what it guesses from the data. I have no idea what triggers each behavior. What's more interesting is reading those dates by the COM interface. Depending on how the user input the data, you'll get formated dates as text or seconds since the epoch as number.
- magicalhippo 6y ago> This is definitely annoying behavior, but you do know that if you format the cell as text prior to pasting the data in it will keep it as text, right? This only works sometimes, and I have no idea when or why. The most reliable way I've found is to copy more than one column and use the text importer thing, where you can specifically mark columns as being text.