Forum Discussion

dejudicibus's avatar
dejudicibus
Copper Contributor
Apr 26, 2020

Stop Excel to interpret date string as dates in text formatted cells

I am really puzzled by the Excel behavior. I have a sheet and I have a column that has format TEXT, so I expect that whatever I put there is interpreted as a string and no attempt is made to guess anything else. However, if I have in the clipboard or I drag & drop in that cell from a web page the string "12 Jan 1980" Excel converts it to a date. I do NOT want that because I have a lot of dates from XVI to XX century and this occurs only for recent "dates". I want to have ALL OF THEM as text, but pre-formatting cells as text seems to be ignored by Excel. Any way to fix that? I hate "smart" programs because they pretend to assume something whatever you really want! 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    This is a 5 year old thread. Much has changed since then. Please open a new thread and mention your Excel version.

  • dejudicibus 

     

    Right Click,  click the button to match destination formatting (circled red)

     

     

    Wyn

    MVP

    Australia UTC+ 8

     

    If this answer was the best response please click the button

    I also happily accept likes

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    dejudicibus Copy/Paste or Drag&Drop overwrites everything in the destination cell. Also the format that you had given the cell. Select Paste Special and find the right option to paste. When I tried copying a date from the web into a text formatted cell, Paste Special offered me a number of paste options. On a Mac it says "Keep source formatting" or "Match destination formatting". On Windows, I get "Keep Text Only".

     

     

    • promnw's avatar
      promnw
      Copper Contributor

      Used to work ... doesnt work

      Still get goofy results, trying to paste 12 - 48 "months", ends up Dec -48, except 12-24 ends up 24- Dec.  I thought there was an OPTION to select to keep this from happeneing.  I cant seem to find how to search OPTIONS .  Thats a deal breaker!

Resources