Pasting Numeric Values in Other Programs

by Allen Wyatt
(last updated August 4, 2018)


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


Replacing and Converting in a Macro

When you use a macro to process data you always run the risk of making that data unusable by Excel. This is especially ...

Discover More

Converting Word to German

Want to convert your usage of Word from one language to another? It's not as easy as one would hope, as you'll discover ...

Discover More

Wildcards in 'Replace With' Text

When doing searches in Excel, you can use wildcard characters in the specification of what you are searching. However, ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Using Copy and Paste for Formatting

Want to copy formatting from one cell and paste it into another cell? It's easy to do if you use the Paste Special ...

Discover More

Deleting Unwanted Styles

Custom styles can be a great help in formatting a worksheet. You may, at some point, want to get rid of all the custom ...

Discover More

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

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

View most recent newsletter.


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 five more than 8?

2018-08-06 10:43:37


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

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

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.