粘贴从不同来源复制的相同文本在Excel中的行为不同 [英] Pasting the same text copied from different sources behaves differently in Excel

问题描述

现在这很奇怪

我们有一个项目，正在从Excel电子表格中读取一些数据。显然，此数据必须采用某种格式。一些字段由数字组成，但应视为文本。

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.

要阻止Excel变得智能并更改单元格类型，我将格式设置为

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

现在这是问题所在：我们要粘贴的一些数字在数字之间有间距。当我们删除空格时，Excel将单元格格式更改为标准，然后将文本转换为2.42805E + 11格式。

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.

但是：只有当文本是从某些来源复制的。如果粘贴从文本框中复制的数字，则在编辑空格时一切都会很好。如果我们从网页上复制完全相同的数字，Excel会更改单元格格式。

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.

有人知道是什么原因造成的，还是知道必须让Excel停止使用格式来精明？

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

编辑：我发现了一个有点特殊的解决方案。我录制了一个宏，该宏使用选择性粘贴功能并将文本作为参数，并用ctrl-v（在该特定电子表格中）覆盖了该宏。奇迹般有效！但是，感觉有点 hacky。有人能想到会适得其反的情况吗？

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?

推荐答案

尝试使用特殊编辑粘贴命令，它将为您提供一些控制选择要处理的数据。

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

要了解底层实际发生的复杂情况，请查看有关剪贴板格式的MSDN 。简而言之，这不是所有Excel的错...

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...

从excel复制数据的常见技巧是将其粘贴到记事本中，然后将其剪切回剪贴板，将所有格式缩小为纯文本。但是，它不会帮助您将数据粘贴到Excel中。

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.

这篇关于粘贴从不同来源复制的相同文本在Excel中的行为不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！