Pasting Numeric Values in Other Programs

Written by Allen Wyatt (last updated August 4, 2018)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


4

When transferring data from his bank's website to Excel, Richard can remove formatting by using Paste Special. He wonders if there is a way to remove formatting when going the other way. The Formula bar in Excel displays unformatted numeric values, but when Richard tries copying to the bank's website, the formatting (like the dollar sign) is transferred too, and the value isn't accepted.

Unfortunately, Excel doesn't have a "copy values" type of function. That being said, there are a number of ways to get around the issue.

First, if you are trying to copy the contents of a single cell to your bank's website, you could simply copy from the Formula bar (which you noted already shows an unformatted value). Just select the contents in the Formula bar, copy (Ctrl+C will do), and then paste to the website.

Another approach that works well—particularly if you are wanting to paste values from multiple cells—is to format the cells as General. This removes things like commas and dollar signs from the cells and allows you to get at the unformatted values. When you are done pasting, you can then press Ctrl+Z to undo the formatting you applied to the cells.

Partially related to this is the idea to make an intermediate copy. In other words, copy the cells and then use Paste Values to paste just the unformatted values into a different worksheet. Provided you didn't format the cells in the worksheet before doing the paste, you end up with unformatted values you can then copy to your bank's website.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13558) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Turning Track Changes Off for Selected Areas

Track Changes is a great tool to use so that you can, well, "track" what changes are made during the development of a ...

Discover More

Understanding the Notification Area

The Notification Area is located at the right side of the taskbar. It represents an active area of information for ...

Discover More

Creating a Master Document Using Existing Subdocuments

If you decide to create a master document, it is easy to do by just adding one or more subdocuments to an existing ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Setting Cell Width and Height Using the Keyboard

Hate to take your hands off the keyboard? Here are a couple of ways you can reject the mouse and still adjust the height ...

Discover More

Changing Currency Formatting for a Single Workbook

Currency is formatted differently in different corners of the world. Most formatting uses periods and commas to indicate ...

Discover More

Losing Formatting

When you save a workbook, you expect Excel to remember the formatting you applied in the worksheets in that workbook. If ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 4 + 0?

2018-08-06 10:43:37

Roy

Something I find handy, especially on state tax websites, is to do the work on one or more pages, then have another page where the results I need to paste are, in the order I need them for the input form and formatted as I need them. Click on the first, Ctrl-C, click the next, go to the form, Ctrl-V to paste it, go back and Ctrl-C the second, click on the third, return to the input form, and so on.

Like having a separate page for each report rather than finding some arrangement of the working area that satisfies every report idea anyone ever has.

So if the bank or government website won't take commas, you just don't have them in the cell's format. Always requires two decimal places even if they are zeros? Format for them. Be sure to have a column of labels or some other way to make sure you don't lose track of which entry is next though often the entries take care of that for you or there are only two or three to keep track of. After I Ctrl-C, I like to select the next cell so that clicking back on Excel once lets me immediately Ctrl-C: no funny business of clicking 23 times while Excel shows from dependent cells in the formula I no longer need before letting me come to the next cell I need.

Got to admit though, it's nicer when the website's idiot programmers take account of things. Don't want dollar signs? It can never hurt to just remove them, maybe flash red while doing so, but just remove them. But if someone pasted something alphanumeric (maybe "3AA45kfj8") don't do so. Just for characters like $, ¢, ",", ".", and in date, phone number, or social security number fields / and -. (Drawing the line somewhere, maybe don't do en dashes even in these kinds of field.)

But since web programmers like the "14yo code kiddee" approach, I s'pose none of that's happening soon. So, try the above.


2018-08-05 12:43:44

J. Woolley

Here is an improved version of the macro I posted yesterday. It accounts for a non-contiguous range. See
http://excel.tips.net/T008701_Stepping_Through_a_Non-Contiguous_Range_of_Cells.html

Sub CopyValues()
Dim oData As New MSForms.DataObject
Dim rSelection As Range
Dim sValues As String
Dim nArea, lastArea, nRow, lastRow, nCol, lastCol
If TypeName(Selection) <> "Range" Then
Beep
Set oData = Nothing
Exit Sub
End If
sValues = vbNullString
lastArea = Selection.Areas.Count
For nArea = 1 To lastArea
With Selection.Areas(nArea)
lastRow = .Rows.Count
lastCol = .Columns.Count
For nRow = 1 To lastRow
For nCol = 1 To lastCol
sValues = sValues + CStr(.Cells(nRow, nCol).Value)
If nCol < lastCol Then sValues = sValues + vbTab
Next nCol
If nRow < lastRow Then sValues = sValues + vbNewLine
Next nRow
End With
If nArea < lastArea Then sValues = sValues + vbNewLine
Next nArea
oData.SetText sValues
oData.PutInClipboard
Set oData = Nothing
End Sub


2018-08-05 00:53:52

Jacques Raubenheimer

Not certain this is relevant, as the assumption is that one would be using formulas in Excel, but if the content in Excel consists of values only, then switching to Show Formulas (Ctrl+`) will allow one to copy the values and then paste them without any formatting.
Of course, if the cells contain formulas, then this will not work, and an intermediate Paste Values as described above will be needed.


2018-08-04 17:11:33

J. Woolley


Here's a little Macro using ideas from http://www.cpearson.com/excel/Clipboard.aspx.
In Excel, press Alt+F11 to open Visual Basic Editor:
1. Pick Insert > UserForm. Under the Forms folder, right-click the new Form and pick Remove....
2. Pick Tools > References. Microsoft Forms 2.0 Object Library should now have a check-mark.
3. Copy the following into a module:

Sub CopyValues()
sValues = vbNullString
For nRow = 1 To Selection.Rows.Count
For nCol = 1 To Selection.Columns.Count
sValues = sValues + CStr(Selection.Cells(nRow, nCol).Value) _
+ IIf(nCol < Selection.Columns.Count, vbTab, vbNullString)
Next nCol
sValues = sValues + IIf(nRow < Selection.Rows.Count, vbNewLine, vbNullString)
Next nRow
Dim oData As New MSForms.DataObject
oData.SetText sValues
oData.PutInClipboard
Set oData = Nothing
End Sub

In Excel, press Alt+F8 to display the list of Macros and pick CopyValues, then pick Options. In the Shortcut Key box, press Shift+C. In the Description box, enter the following text: Copy values (without formatting) from the Selection to the Windows clipboard. Now select a range of cells on a sheet and press Ctrl+Shift+C, then paste the clipboard into a text file (for example) to see the results.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.