Excel question: text formatting doesn't work

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


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



or whatever syntax you need.


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)