title: Excel paste special using values only, also copies the data type of the value along with the value into the destination cell
body: <p>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.  </p>
  
  <p>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</p>
  
  <p>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.</p>
  
  <p>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)   </p>
  
  <p>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.</p>
  
  <p>D. Entering <code>=SUM(1,2)</code> into a cell with a text data type causes the formula to be displayed and not calculated.  ie it is treated as text  data.</p>
  
  <p>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. </p>
  
  <pre><code>Public Function ConvertValueToHaveTextDataType(Avalue As Variant) As String
  
      ConvertValueToHaveTextDataType = CStr(Avalue)
  
  End Function
  </code></pre>
  
  <h2>Here's why I came to these conclusions.</h2>
  
  <p>I've been experimenting and found:</p>
  
  <ol>
  <li><p>Loading data into worksheet cells using <code>QueryTables.TextFileColumnDataTypes</code>   has a parameter <code>TextFileColumnDataTypes</code>   which can be assigned <code>xlTextFormat, xlGeneralFormat</code> and some date formats.  There is no numeric option.  So using the <code>xlGeneralFormat</code> to load numeric data seems to be "how it's done".</p></li>
  <li><p>Based on this I assume each Excel cell has a "data type" (ie General, text or date).  </p></li>
  <li><p>"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).</p></li>
  <li><p>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)</p></li>
  <li><p>This is where it gets weird.  </p></li>
  </ol>
  
  <p>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!).  </p>
  
  <p>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"</p>
  
  <ol start="6">
  <li><p>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.<br>
  Note that if you were to use <code>LEFT({acell stroing '0123},1)</code> 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.</p></li>
  <li><p>The formula <code>=TEXT("123","0###")</code> 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.</p></li>
  <li><p>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.   <code>=T(0123) = ""    =T("0123") = 0123</code></p></li>
  <li><p>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.</p></li>
  <li><p>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.</p></li>
  </ol>
  
  <p>Did anyone read this far?</p>