to remove formatting from data saved to clipboard, and then paste to Excel.
Pure Text can be invoked by clicking on its toolbar icon.
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).
- choose delimited,
- indicate delimiters and probably treat multiple spaces as one;
- choose which columns are to become text by selecting the column(s) and then choosing text for the format.
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.
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 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:
Text 02-Jan 0-1 1-0 111-1 1-111 2001-01-01 31-1-32 1-48 Text 02-Jan 0/1 1/0 111/1 1/111 2001-01-01 31/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)
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 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 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.
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.
to remove formatting from data saved to clipboard, and then paste to Excel.
Pure Text can be invoked by clicking on its toolbar icon.
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).
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
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:
- Copy any formatting: interior color, number formats, borders, conditional formats
- Can be used to clear formatting by copying formatting of a cell(s) to in effect work like Edit, Clear, Formats. But if you had borders on in from the cell(s) you copied, unliked Edit, clear, formats, you would retain your borders.
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.
- 2000-07-04, Unprotect a sheet, Tom Ogilvy, programming
- 1999-10-22, Button (take focus), Tom Ogilvy, programming
- 2003-06-27, Format Painter in Customizing Keyboard Commands, Alex B.
- 2003-07-08, comments, tested in XL97, XL2002, Dave Peterson, programming
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
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
PureText 2.0, Steve
P.Miller, paste copied text as simple text into another application
without getting all the formatting from the original source
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2009, F. David McRitchie, All Rights Reserved