Written by Allen Wyatt (last updated June 22, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
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, Excel in Microsoft 365, and 2021.
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!
The Format Painter is great for copying formatting from one cell to another. If you don't want to grab the mouse to use ...
Discover MoreHave you ever seen a worksheet in which some zero values have a negative sign in front of them? There's a reason for ...
Discover MoreExcel allows you to adjust spacing between cell walls and the contents of those cells. It does not, however, allow you to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-06-22 14:55:26
J. Woolley
My Excel Toolbox includes the CopyValues macro (Ctrl+Shift+C) to copy unformatted cell values from a selected range to the Windows clipboard, which can then be pasted into a website or other document.
See https://sites.google.com/view/MyExcelToolbox/
For a general purpose way to paste values without formatting, consider the Microsoft Store's PureText app. See https://stevemiller.net/PureText/
For more on this subject, see https://excelribbon.tips.net/T013669_Changing_the_Default_Paste_Mode.html
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