Mac Excel 2011: Formatting a column as text doesn’t stop 8/40 from being Aug-40 until it’s formatted again as text, at which point I get a number I think is a Julian calendar date.
Ideas on stopping this? I often have to copy/paste class enrollments of 10/27 and such. Thanks.
ok, it becomes 40-Aug.
Didn’t completely understand but I think that if you format the empty cells as text and then “paste special” - “Text” you’ll get the result you want.
1 - Set column attributes
2 - Paste
or
1 - Paste
2 - Set columns that need adjustment
3 - Re-paste
Put an apostrophe (single quote mark) before the 8/40.
I see the paste special point.
I’m copying multiple lines from web sites, gathering data on a university from tables. I can’t put an apostrophe before just this one column as I paste.
I had formatted the column before pasting. That wasn’t working.
If your data is in column A then in column B put in this formula ="’"&a1. That renders your data permanently text. Copy and Paste-special as values back to column A. That’s a " with a ’ followed by another ".
You could put an extra column with a formula for the calendar date
TEXT(C2,“yyyy-mm-dd”)
TEXT(C2,“mm/yy”)
or whatever syntax you need.
http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx
When I try a similar exercise in Win Excel 2010, I have a (usually annoying) pulldown menu next to the paste area that allows changing how it was pasted, labeled “Paste Options”.
Within this menu is an option to use a Text Import wizard, just like you might in the “Data/Text to Columns” menu… delimiting and assigning format types by individual column.
Using that wizard, I can force a column full of slash-divided integers (which Excel stupidly assumes is a date) into Text (retaining the number/number layout) AS I PASTE. So it doesn’t initially get treated as a date (and broken in the process).
It seems to work, assuming the data you’re copy/pasting is structurally consistent enough. (same columns separated by the same delimiters)