Written by Allen Wyatt (last updated March 19, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Applying different formatting to the text within a cell can seem a bit confusing. This is certainly the case when it ...
Discover MoreExcel allows you to adjust not only the horizontal alignment of values in a cell, but also the vertical alignment. This ...
Discover MoreWhen you save a workbook, you expect Excel to remember the formatting you applied in the worksheets in that workbook. If ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments