Pasting the same text copied from different sources behaves differently in Excel

Question

Now this is a weird one

We have a project where we are reading some data from an Excel spreadsheet. Obviously this data has to be in a certain format. Some of the fields consists of numbers, but should be treated as text.

To stop Excel from being "smart" and change the cell types, I have set the format in the respective cells to 'text'.

Now here is the problem: some of the numbers we're pasting have spacing between the digits. When we remove the white spaces, Excel change the cell format to 'standard' and turn the text into the 2.42805E+11 format.

BUT: this only happens when the text is copied from some sources. If a paste a number copied from a textbox, everything turn out fine when we edit the spaces. If we copy the exact same number from a web page, Excel change cell format.

I thought copy-paste would be copy-paste, but obviously some formating or something gets along on the ride.

Does anyone know what causes this, or know have to get Excel to stop being "smart" with the formating?

EDIT: I found a somewhat peculiar solution to this. I recorded a macro that uses the 'Paste Special' function with text as parameter, and overrided ctrl-v with it (in that particular spreadsheet). Works like a charm! Feels a bit "hacky", though. Can anyone think of a scenario where this will backfire?

Answer 1

Try using the Edit Paste Special command, it will give you some controls to choose what to do with the data.

For a taste of the complexity of what is really going on underneath, look in MSDN about Clipboard Formats. In short, it isn't all Excel's fault...

A common user trick copying data out of excel is to paste it into Notepad and cut it back to the clipboard, which flattens all the formatting down to plain text. It won't help you for pasting data into Excel, however.

Answer 2

Copy-paste in windows retains formatting. One way to get rid of the formatting is to paste the text into e.g. notepad first, then select and copy it again. This loses any copied formatting.