The "Text" formatting of Excel 2010 has the following description:

The text is displayed exactly as entered.

However, that's simply not true. Whenever I past stuff into Excel, Excel turns the text into a date format (e.g. 1/2 becomes Jan-1). Furthermore, zeros at the end of numbers are cut off if the cells aren't long enough (e.g. 1.100 becomes 1.1). Turning the text back into "Text" format is not solution. Pre-formatting all cells as "Text" has no effect.



If have literally spend an hour searching for a solution and couldn't find one. The best thing I found is adding an apostrophe in front every "date", but this creates so much additional work that I don't consider it to be a solution.

Can anybody help?

Preformatting to text works for me. See attached.

Attached Files

Lebber.xlsx (7.9 KB, 8 views)

Doesn't work for me. E.g. if I paste this (copied from this very post):

2/4

into the table in a cell formatted as "Text", it becomes 02-Apr. Pasting from certain formats (.txt) does the job, but I would like it work all the time.

When you standard paste (cntl-v) onto a cell like that, you are pasting everything, including the format.

When you copy "2/4" from here, you are copying the text and the format though the format is a null (for lack of a better term). So when you standard paste, Excel enters the data and sees the null format. It then assumes date and formats it as such. It can be quite frustrating as you know. However, it is a time saver most of the time.

You can get around this with a special paste:
* Make sure the cells are preformatted as text.
* Copy the text.
* Click the dropdown below the Paste button. You will see two icon options. If you hover over each, they say
----Keep source formatting (M): this is the standard paste
----Match destination formatting (K): this is a text only paste
* Select the latter, and it will display the way you are looking for.

I'm not really sure what the letters in parentheses above signify. They don't appear to be shortcuts.

That does indeed prevent Excel from reformatting numbers into dates, but it also destroys the rest of the format and all text gets cramped into a single cell or awkwardly stretched with lots of empty cells between. Format is okay, I just don't want any no reformatting.

Thanks anyway, though. I guess there's just no way to turn off that stupid date formatting.

It's hard to say. Not having seen what you are trying to do, I don't know if there is any way to work around it. Post your file. Maybe fresh eyes will help.

Indeed, there is none that I know of. You would think there could be an option to disable it for a sheet, but perhaps it's more complicated than we can imagine to do so.

I tried this on different occasions. Here's an example: I would like to copy the stuff from these forums posts (just the table stuff) into an Excel table. It works finde, with the exception of Excel re-formatting the numbers. If I copy it into a text editor, the fromat is gone. I want to keep the fromat, I just don't want it to be changed.

Late reply, but this still comes up in searches: It's not possible to turn of date formatting in Excel (at least not up to 2010). You can create workarounds, but date formatting is hardcoded.

That has never been true... Excel supports any valid date format (and even some that aren't valid).

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

And how can you turn it off?

There is nothing to turn off. Pasting dates results in then taking whatever the default date format is for your system. If you want to use another format, either paste them as values into pre-formatted cells (otherwise you'll just get serial #s) or paste then re-format.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I want to be able to paste somethign like "24-7" as "24-7" and NOT transform it in any way. That's precisely the issue I have.

They will still be transformed. Not a solution.

Inefficient and obviously not a solution either.

Evidently, you've never heard of preformatting the destination as text... Content like '24-7' pasted from another application will remain '24-7'. Likewise, it seems you've never explored the 'Paste Special 'options, one of which is 'Keep source formatting'...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Both of which are broken. I can either not get the dates and lose the rest of the formatting, or get the dates: https://youtu.be/1h35UT4kAzU

Exactly

Tested from Notepad , works perfectly

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post