Excel仅使用值粘贴特殊功能，也会将值的数据类型与值一起复制到目标单元格中 [英] Excel paste special using values only, also copies the data type of the value along with the value into the destination cell

问题描述

我一直在试图更好地了解excel单元格，特别是他们的数据类型。如果任何人对细节感兴趣，我的调查在下面的数字。我的结论被标记为A到D.我真的很有兴趣有人有任何补充。

I've been trying to understand excel cells more and specifically their data types. If anyone is interested in the detail my investigation is in the numbered points below. My conclusions are labelled A to D. I'm really interested in whether anyone has anything to add.

A。每个excel单元格都有一个属性来定义它将存储的数据的预期数据类型。存储在单元格中的数据值也具有数据类型属性，不必与单元格数据类型属性相同。 （数据类型为常规，文本或日期）。 （单元格的数据类型与使用格式单元格>数字选项卡格式类别的单元格格式设置不同，但与...相关

A. Each excel cell has a property that defines the expected "data type" of the data that it will store. The data value stored in a cell also has a "data type" property, that does not have to be the same the the cell "data type" property. (Data types are General, Text or Date). (The Cell's data type is not the same as the cells formating set using "Format Cell">"Number tab" "format category" but it is related

B。将数据输入到一个单元格中，给数据值的数据类型从单元格的数据类型继承（当数据值可以转换为单元格数据类型时），如果数据在开始时包含一个导致文本数据类型被分配为值的数据类型，而不管细胞数据类型。

B. When data is entered into a cell, the data type given to the the data value is inherited from the cell's data type (when the data value can be converted into the cell data type). If the data contains a ' character at the start, it causes the text data type to be allocated as the value's data type, regardless of the cells data type.

C。当您使用excel copy>粘贴（仅限值）将数据值从一个单元格复制到另一个单元格，数据类型也被复制（这是一个很棘手的事情，因为在这个特殊的粘贴中没有任何覆盖，你可以用PasteSpecial> Raw选项来实现）

C. When you use excel copy>Paste(Values Only) to copy the data value from one cell to another, the data type is also copied. (This is a bit nuts as there is no override to this in paste special. You could kind of do with a PasteSpecial>Raw option)

注意：如果从文本文档（例如01234）粘贴数据，则使用默认粘贴将导致文本0123转换为一个数字。用数据类型o粘贴aa值f文本 - 如果单元格的数据类型是文本。

Note: If you paste data in from a text document (eg 01234), using the default paste will cause the text 0123 to be converted into a number. Using Value Only paste, the 01234 is pasted a a value with data type of text - if th cell's data type is text.

D。将 = SUM（1,2）输入具有文本数据类型的单元格将导致公式被显示而不计算。即将其视为文本数据。

D. Entering =SUM(1,2) into a cell with a text data type causes the formula to be displayed and not calculated. ie it is treated as text data.

请注意，此VBA函数可用于转换存储在单元格中的数字值的数据类型以具有文本数据类型具有相同的字符。只需使用公式参考要转换的单元格。 Yu可以使用copy> paste special（仅限值）将转换的值移动到单元格中，以便将其存储为一个值。

Note that this VBA function can be used to convert the data type of a number value stored in a cell to have the text data type with the same characters. Simply reference the cell you want to convert with the formula. Yu can use copy>paste special(Values Only) to move the converted value into a cell so it's then stored as a value.

Public Function ConvertValueToHaveTextDataType(Avalue As Variant) As String

    ConvertValueToHaveTextDataType = CStr(Avalue)

End Function

这就是为什么我得出这些结论。

我一直在尝试并发现：

Here's why I came to these conclusions.

I've been experimenting and found:

1. 使用 QueryTables.TextFileColumnDataTypes 有一个参数 TextFileColumnDataTypes 可以分配 xlTextFormat，xlGeneralFormat 和一些日期格式。没有数字选项。因此，使用 xlGeneralFormat 加载数字数据似乎是如何完成。

1. Loading data into worksheet cells using QueryTables.TextFileColumnDataTypes has a parameter TextFileColumnDataTypes which can be assigned xlTextFormat, xlGeneralFormat and some date formats. There is no numeric option. So using the xlGeneralFormat to load numeric data seems to be "how it's done".

基于我假设每个Excel单元格都有一个数据类型（即常规，文本或日期）。

Based on this I assume each Excel cell has a "data type" (ie General, text or date).

格式化单元格将改变数据的显示方式，还会改变数据输入时的行为方式，即更改单元格格式也可以更改单元格数据类型。当单元格格式为TEXT时，输入01234将导入输入的数据作为文本存储，并保持前导0。当一个单元格的GENERAL格式输入0123将导致它被存储为数字123（即注意0被删除）。

"Format Cells" will change how data is displayed, but also changes how they behave when data is being input ie changing the cell format can also change a cells "data type". When a cell format is TEXT, entering "01234" will cause entered data to be stored as text and the leading "0" is kept. When a cell has GENERAL format entering "0123" will cause it to be stored as the number 123 (ie note the 0 is removed).

考虑一个单元格TEXT格式和0123作为文本值存储。将单元格的格式更改为GENERAL或NUMBER将不会更改存储在单元格中的字符，但会向单元格添加一个绿色标记，提供弹出式警告，通知您存储为文本的数字。 （Paste special，multiply可以将它们转换成一个数字）

Consider a cell with TEXT format and "0123" stored as a text value. Changing the format of the cell to GENERAL or NUMBER will not change the characters stored in the cell, but will add a green tag to the cell that provides a "popup warning" that informs you "Number Stored as Text". (Paste special,multiply can be used to convert these to a number)

这是很奇怪的地方。

如果您按ENTER键编辑存储在通用格式单元格中的文本值0123 0被删除，它变为123，这是你期望的。但是，如果您复制0123单元格并使用VALUE ONLY选项进行粘贴，则将数据粘贴为0123，如果将其粘贴到数字格式单元格中，则会发生相同的情况。所以当你使用excel copy> Paste（Values Only）复制数据值时，数据类型也被复制。 （这是坚果！）。

If you edit the text value "0123" stored in a "General format cell" on pressing the ENTER key the "0" is removed and it become 123, which is what you would expect. However if you, copy the "0123" cell and paste it using the "VALUE ONLY" option, the data is pasted as "0123", the same thing happens if you paste this into a number Format cell. So when you use excel copy>Paste(Values Only) to copy the data value, the data type is also copied. (This is nuts!).

如果将123.12存储为数字或通用格式单元格的数字，则转换为文本格式单元格，值保持为123.12，单元格类型保持为文本，不显示弹出式窗口显示警告，我不知道该值的数据类型是什么。但是，如果您编辑单元格并按RETURN输入123.12，则会收到一个警告标签，弹出窗口，将其显示为以文本形式存储

If you paste 123.12 stored as number from a numeric or general format cell, into a text format cell the value remains as 123.12 and the cell type remains as text, no popup warning is shown, I'm not sure what the data type of the value is. However if you edit the cell and press RETURN to enter 123.12, you do get a warning tag and popup saying "number stored as text"

1. 如果要将数字0123输入到具有NUMERIC格式的单元格中，并将其存储为文本0123，则可以进一步复杂化，您可以将其输入为0123表示excel应该存储该值与文本的数据类型。
   这是有用的。
   
   请注意，如果您要使用 LEFT（{acell stroing'0123}，1）它将返回 0\" 。没关系另请注意，如果您将一个数字格式的单元格复制并粘贴到一般格式单元格中，那么单元格将不再显示，并且您将获得一个绿色弹出警告号码作为文本存储。

1. As a further complication if you want to enter a number 0123 into a cell with NUMERIC format, and have it stored a text "0123" you can enter it as '0123 the ' indicates that excel should store the value with a data type of text. This is useful.
   Note that if you were to use LEFT({acell stroing '0123},1) it would return "0". Which is ok. Also note that, if you copy and paste (values only) a numeric format cell storing '0123 into a general format cell, the ' is no longer displayed for the cell and you get a green popup warning "number stored as text". Which is Ok, but worth being aware of.

公式 = TEXT（123，0 ###） 创建一个带有文本数据类型的值。如果您复制并粘贴结果，它将成为具有文本数据类型的数据值。

The formula =TEXT("123","0###") creates a value with a text data type. If you copy and paste the result, it becomes a data value with the text data type.

公式T很有用，因为它可以用于查找是否存储在单元格中的值的数据类型为TEXT。 = T（0123）== T（0123）= 0123

The formula T is useful as it can be used to find whether the data type of a value stored in a cell is TEXT or not. =T(0123) = "" =T("0123") = 0123

引用在单元格值中加1的公式中的单元格，单元格数据类型和值的数据类型与计算没有差别。数据值被转换成一个必需的数字，隐含地。

If you reference a cell in a formula that adds 1 to the cell value, the cell data type and the data type of the value make no difference to the calculation. The data value is converts to be a number a necessary, implicitly.

正如你所料，将数据粘贴到单元格中，如果您选择匹配目的地格式化和您将数据粘贴到使用TEXT格式的单元格，任何带有0的数据将保留0。

As you would expect, when pasting data into cells, if you choose to match the destination formatting and the cells you are pasting the data into uses the TEXT format, any data with leading "0"'s will have the 0's kept.

有没有人读过这个文件？

Did anyone read this far?

推荐答案

如果要在数字上显示前面的零，您可以在小数点之前具有固定数字位数，您可以自定义格式化单元格，例如0000.00。
此示例将始终显示小数点前后的四位数字，之后的两位数字;它有其限制，但对于例如非常有用。产品代码。如果您使用7位数的代码进行标准化，则将自定义格式设置为0000000，0001234将保持正确（不是1234），并且仍然会被识别为一个数字。

If you want to show preceding zeroes on a number, providing you have a fixed number of digits before the decimal point you can Custom format the cells as e.g. '0000.00'. This example will always show four digits before the decimal point and two afterwards; it has its limits but is very useful for e.g. product codes. if you standardise on 7-digit codes, set the custom format as '0000000' and '0001234' will stay correct (not '1234') and still be recognised as a number.

这篇关于Excel仅使用值粘贴特殊功能，也会将值的数据类型与值一起复制到目标单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！