Pasting Numeric Values in Other Programs

by Allen Wyatt
(last updated August 4, 2018)

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 Office 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

Determining If a Value is Out of Limits

Need to figure out if a value is outside of some arbitrary limit related to a different value? There are a number of ways ...

Discover More

Removing Tabs Used to Indent a Paragraph

You get a document from a colleague and you notice that each paragraph starts with a tab character. Here are a couple of ...

Discover More

Copying Pictures with a Macro

Copying information using a macro is rather simple, although there are multiple ways you can do the copying. The most ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Underlining Text in Cells

Want a quick way to add some underlines to your cell values? It's easy using the shortcuts provided in this tip.

Discover More

Understanding Underlines

Excel provides a variety of underlining styles you can use when you need to underline information within a cell. Here's ...

Discover More

Understanding Monospace Fonts

Information in a worksheet needs to be displayed using fonts. If you understand the two different types of fonts ...

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}] 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 9 - 6?

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.