# Excel question: text formatting doesn't work **URL:** \ **Category:** Factual Questions\ **Created:** [August 25, 2014, 4:49pm UTC](https://boards.straightdope.com/t/excel-question-text-formatting-doesnt-work/696465 "2014-08-25T16:49:26Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Cardinal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cardinal/32/4000_2.png) [@Cardinal](https://boards.straightdope.com/u/Cardinal)\ **Post date:** [August 25, 2014, 4:49pm UTC](https://boards.straightdope.com/t/excel-question-text-formatting-doesnt-work/696465/1 "2014-08-25T16:49:26Z")
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. ---
**Author:** ![Cardinal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cardinal/32/4000_2.png) [@Cardinal](https://boards.straightdope.com/u/Cardinal)\ **Post date:** [August 25, 2014, 4:53pm UTC](https://boards.straightdope.com/t/excel-question-text-formatting-doesnt-work/696465/2 "2014-08-25T16:53:19Z")
ok, it becomes 40-Aug. ---
**Author:** ![tim-n-va](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/tim-n-va/32/3001_2.png) [@tim-n-va](https://boards.straightdope.com/u/tim-n-va)\ **Post date:** [August 25, 2014, 5:03pm UTC](https://boards.straightdope.com/t/excel-question-text-formatting-doesnt-work/696465/3 "2014-08-25T17:03:05Z")
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. ---
**Author:** ![RaftPeople](https://avatars.discourse-cdn.com/v4/letter/r/6f9a4e/32.png) [@RaftPeople](https://boards.straightdope.com/u/RaftPeople)\ **Post date:** [August 25, 2014, 5:07pm UTC](https://boards.straightdope.com/t/excel-question-text-formatting-doesnt-work/696465/4 "2014-08-25T17:07:31Z")
1 - Set column attributes 2 - Paste or 1 - Paste 2 - Set columns that need adjustment 3 - Re-paste ---
**Author:** ![Dewey\_Finn](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dewey_finn/32/4222_2.png) [@Dewey\_Finn](https://boards.straightdope.com/u/Dewey_Finn)\ **Post date:** [August 25, 2014, 5:10pm UTC](https://boards.straightdope.com/t/excel-question-text-formatting-doesnt-work/696465/5 "2014-08-25T17:10:47Z")
Put an apostrophe (single quote mark) before the 8/40. ---
**Author:** ![Cardinal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cardinal/32/4000_2.png) [@Cardinal](https://boards.straightdope.com/u/Cardinal)\ **Post date:** [August 25, 2014, 9:18pm UTC](https://boards.straightdope.com/t/excel-question-text-formatting-doesnt-work/696465/6 "2014-08-25T21:18:11Z")
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. ---
**Author:** ![Magiver](https://avatars.discourse-cdn.com/v4/letter/m/4491bb/32.png) [@Magiver](https://boards.straightdope.com/u/Magiver)\ **Post date:** [August 25, 2014, 11:18pm UTC](https://boards.straightdope.com/t/excel-question-text-formatting-doesnt-work/696465/7 "2014-08-25T23:18:41Z")
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 ". ---
**Author:** ![gigi](https://avatars.discourse-cdn.com/v4/letter/g/a587f6/32.png) [@gigi](https://boards.straightdope.com/u/gigi)\ **Post date:** [August 28, 2014, 7:24pm UTC](https://boards.straightdope.com/t/excel-question-text-formatting-doesnt-work/696465/8 "2014-08-28T19:24:43Z")
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. > **[Microsoft Support](https://support.microsoft.com/en-us?correlationid=477d332d-f91d-436e-a7b9-96047a564f54&ui=en-us&rs=en-us&ad=us)** > > Microsoft support is here to help you with Microsoft products. Find how-to articles, videos, and training for Microsoft 365, Windows, Surface, and more. ---
**Author:** ![gnoitall](https://avatars.discourse-cdn.com/v4/letter/g/bb73d2/32.png) [@gnoitall](https://boards.straightdope.com/u/gnoitall)\ **Post date:** [August 28, 2014, 8:43pm UTC](https://boards.straightdope.com/t/excel-question-text-formatting-doesnt-work/696465/9 "2014-08-28T20:43:18Z")
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)