Paste / Pasting. Cut & Paste

Location: http://www.mvps.org/dmcritchie/excel/paste.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

Pasting Data from a webpage   (#webpage)

The problem:
Pasting data from a web page that was not created using an MS Office product can present problems because anything that looks like a date will be assumed to be a date.  The reason MS Office created pages would work is because they have lots of round tripping code including formulas, and formatting (and typically are at least 3 times the size of simple HTML code).

Source: data from a web page, copied (ctrl+c) using IE

Excel will attempt to format anything that resembles a date as a date, so that 1-18 becomes Jan 18.  If you are manually entering your data, you simmply format the cells ahead of time as text, but you don't always have a choice.

It may not be clear in each of the following, but format your cells ahead of time as text.

Manual entry:
You have to format the cells as text before you enter data into a cell.

Reading a file:
When you Export data, save the file as .txt instead of .csv then when you import the file you will be given a choice of your formatting -- choose text for each column within the dialog by selecting the column then choosing text.  The "file import wizard" and the "text to columns" work exactly the same see use of text to columns later.

Cut and Paste:  from HTML that was not generated from Excel or MS Office
Don't think you can do anything, even though you format your Excel sheet as text ahead of time.  Excel will attempt to format many numbers with two or less hyphens as dates.

You might with some data be able to paste to notepad, then from notepad cut and paste to Excel.  It might paste as wanted.  OR it might paste all to one column.

If it pastes all to one column then use Data, Text to columns,

You might find that the tab character was retained by notepad and you do not have to use Text to Columns (or the Text Import Wizard).

Avoid Notepad as a middle step:
If you have to do this often you can convert what you copied to text within the clipboard using  Pure Text, free to use, not to distribute, and worth every penny.  My options are exactly as shown on the web page, and the options don't change what it does.

Test data you can try: (with built in surprises)
Note one of the columns is empty (just before 111-1), also note rows 3 & 4 have Char(160) the nonbreaking space character (used one byte instead of   but is the equivalent), and a descrepency in the first column for the word "text".  Results of copy from the table and pasting directly into Excel will vary depending on your Regional parameters for date entry.
 

 Text1-20-11-0 111-11-1111-1-131-1-321-48
Text1/20/11/0 111/11/1111/1/131/1/321/48
 Text 1-2 0-1 1-0   111-1 1-111 1-1-1 31-1-32 1-48
 Text 1/2 0/1 1/0   111/1 1/111 1/1/1 31/1/32 1/48

Your results from a direct paste and copy would be based on your regional date settings, but might look something like the following:

 Text02-Jan0-11-0 111-11-1112001-01-0131-1-32 1-48
Text02-Jan0/11/0 111/11/1112001-01-0131/1/32 1/48
 Text 1-2 0-1 1-0   111-1 1-111 1-1-1 31-1-32  1-48
 Text 1/2 0/1 1/0   111/1 1/111 1/1/1 31/1/32  1/48

Your results from use of Notepad or PureText before pasting into Excel might look like the following:  (Don't forget to format Excel cells as text beforehand)

 Text1-20-11-0 111-11-1111-1-131-1-321-48
Text1/20/11/0111/1 1/1111/1/131/1/321/48 
 Text1-20-11-0 111-11-1111-1-131-1-321-48
 Text1/20/11/0 111/11/1111/1/131/1/321/48

Still have a problem?
I tested this using Windows 2000, Excel 2000, yyyy-mm-dd as short Regional Date format and copied using both IE (Internet Explorer 6.0) and with Firefox 1.0 (Mozilla Firefox), changed the data within the clipboard with PureText before pasting to a worksheet preformatted as text.

Did you remember to format the worksheet as Text beforehand.
Did you convert one table at a time instead of trying several tables at once.


Pasting Without including Formatting     (#withoutformatting)

There are several methods you can use to copy a value or a formula from another cell or from HTML into one cell, or from multiple cells or HTML into multiple cells.  Applies also from any other application as well.

Pasting Values     (#values)

Macros to Paste Values.  Some people with laptops can use the keypad well enough to prefer a macro with shortcut keys instead of moving cursor up to toolbar and back and instead just use the mouse to designate the cells in source and destination.  Macro uses values from Copy (Ctrl+C) source.
Sub PasteSpecialValues()   'assign macro to  Ctrl+SHIFT+V
 On Error Resume Next
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
 If Err.number = 1004 Then
    MsgBox "Can't Paste Special Values from Empty Clipboard" _
      & Chr(10) & "or dimension of multiple cells does not" _
      & " match clipboard" _
      & Chr(10) & Err.number & " " & Err.Description
 ElseIf Err.number <> 0 Then
    MsgBox Err.number & " " & Err.Description
 End If
 Application.CutCopyMode = False     'Clear Clipboard
End Sub
While the suggested shortcut Ctrl+Shift+V sounds logical for this macro it is also logical for other macros, and there is a builtin shortcut that uses the same keyboard combination.  Something else to keep in mind when you use a macro is that you lose the use of UNDO (Ctrl+Z).

Clear Clipboard     (#clear)

Excel has it's own clipboard, which can cause all sorts of problems.  Basically in order to paste anything from the clipboard, Excel must be up when you copy something to the clipboard in my experience.  See Chip Pearson's site for more information on the clipboard.
Sub ClearClipBoard()
   Application.CutCopyMode = False
End Sub

Pasting Formats, as in Format Painter     (#painter)

The shortcut Ctrl+SHIFT+P was chosen for the "P" in the Format Painter.  Without the addition of Selection.select the macro will work for a destination size matching the copied range, or to expand a single cell to the size of the copied area like the Format Painter, but it will not match the format painter's ability to paste to smaller or larger sizes, or multiple ranges. 

By including  Selection.select  you can now chose a smaller or larger size as well as the same size and single cell selections.  This is curious to me that Selection.select would have any affect on anything.

Macro uses the formatting from Copy (Ctrl+C) source.

The format painter can be double-clicked and continue with additional format painting, so the macro does not clear the clipboard to allow continuing.

Sub PasteSpecialFormats()   'assign macro to Ctrl+SHIFT+P
    If Application.CutCopyMode = False Then
       MsgBox "Clipboard is empty or does not have cell formats"
       Exit Sub
    End If
    Selection.Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

A1                                                                              
     B2                                                                            
         C3                                                                     
                                                                                
                                                                                 
                        

Additional uses:

Additional Comments: 
Other than being able to assign a shortcut key, this macro offers no practical advantage over the builtin format painter toolbar icon, but it was interesting looking at these things in more detail.

I am using Excel 2000 and did have the problem invoking from the toolbar button, and from Alt+F8 without the selection.select added but retested next with and without day and it worked in both cases, had not rebooted, but had recycled Excel.  So it it is inconsistent in my mind in XL2k, and Excel 97 apparently does have a problem see Dave Peterson's response below.

The test for clipboard being empty is not really testing that the clipboard is empty, but if there is something in the clipboard, it was not copied from Excel; therefore, would not have formatting to add or remove from other cells.

Some references on Selection.Select or ActiveCell.Activate to change the Focus.

Pasting Formulas     (#formulas)

Chip Pearson uses a right-click menu with associated macros to paste formulas for a single cell
http://google.com/groups?threadm=uNR1fmHt%24GA.287%40cppssbbsa05
also see http://groups.google.com/groups?threadm=uiqh89AxAHA.1620%40tkmsftngp05
be sure to include this line with the above codings in your personal.xls!Auto_open
  Application.CommandBars("Cell").Reset 'was not in 2001-04-13 posting

Pasting Modified Formula   (#modFormula)

Based on a request to place a formula such as =Sheet1!N2 into the clipboard incremented by 1 row to be pasted as =Sheet1!N3
Option Explicit
Sub CopyAddrRowPlus1()
    'D.McRitchie, misc, 2003-08-01,  paste.htm#modFormula
    'convert  =sheet14!A8  to =Sheet14!A9 into clipboard
    Dim txt As String, frml As String, msgx As String
    Dim newTxt As String
    txt = ActiveCell.Formula
    msgx = "malformed pattern reference"
    newTxt = "'ERROR**"
    If Left(txt, 1) <> "=" Then
      msgx = "missing ""="" sign at beginning of formula,"
      GoTo malformed
    End If
    On Error GoTo malformed
    newTxt = Left(txt, InStr(1, txt, "!")) & _
      Range(Mid(txt, 2)).Offset(1, 0).Address(0, 0)
    GoTo done
malformed:
    MsgBox msgx _
       & Chr(10) & "expected something like =sheet14!A8" _
       & Chr(10) & "instead found   " & txt
done:
    'see p://www.cpearson.com/Excel/clipboard.aspx 
     Dim x As New DataObject
     x.SetText newTxt
     x.PutInClipboard
End Sub

This page was introduced on June 28, 2003. 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2009,  F. David McRitchie,  All Rights Reserved