Forum Discussion
dejudicibus
Apr 26, 2020Copper Contributor
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_EekelenPlatinum Contributor
This is a 5 year old thread. Much has changed since then. Please open a new thread and mention your Excel version.
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_EekelenPlatinum 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".
- promnwCopper 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!