If everything you do still ends up pasting text into the cells, you might consider using a macro to convert the cells back to their desired numeric formatting. The formatting of the cell should not be disturbed in the least. There you can press Ctrl+V to paste the value and then press Enter. In other words, select the cell and then click once in the Formula bar. Once you select the destination cell, you should paste into the Formula bar instead of into the cell itself. I should mention another approach you can use if you are pasting values that will appear in a single cell. You also can copy the massaged data and paste it in that destination using the Paste Special dialog box using the techniques already described. That way you won't mess up your eventual destination worksheet. There is one thing you may want to do if you find yourself needing to massage the data in Excel prior to placing it in your worksheet-do the initial pasting and massaging on a different worksheet than your final destination. The only thing that can be done in this instance is to paste them into Excel and then use the Text to Columns tool (on the Data tab of the ribbon) to separate the values into multiple cells, at which point additional cell formatting may be necessary on my part. This causes Excel to place the multiple values into a single cell, and no amount of cajoling will cause Excel to see them as numeric values. However, those values are separated not by tab characters (which would tell Excel that the values should go into multiple cells) but by multiple spaces. For instance, I may end up copying from the source program multiple values that should end up in multiple cells in the Excel worksheet. Not even this approach has worked for some of my pasting operations. This causes Excel to add whatever is in the Clipboard to the target cells, which can (with some source data) provide the desired conversion to numeric values that is necessary to retain the destination formatting. In this case, however, you want to select not only the Values radio button, but also the Add radio button. (Again, it depends on the source of the information you are trying to paste.) If that is the case, then try displaying the Paste Special dialog box previously described. It is possible, though, that this simple approach may not work as you expect.
If your data is now in the worksheet using the desired destination formatting, you are good to go. (Which option is visible depends, for the most part, on the characteristics of your source data.) If you don't see any of these three options available, choose Paste Special and at least one of them should be available in the resulting dialog box. (See Figure 1.)Īny of these three options is designed to do essentially the same thing-simply paste values without any formatting from the source. On the resulting palette of options or resulting Context menu, you want to choose Paste Values, Keep Text Only, or Match Destination Formatting. At the left side you'll see the Paste tool you want to click the down arrow at the right of this tool. Instead, display the Home tab of the ribbon. Select the cell where you want the information pasted, but don't click Ctrl+V. This copies the numbers to the Clipboard, and you can switch over to Excel.
Regardless of how the numbers are output, select them and press Ctrl+C. How the numbers are output can matter to how you paste them into Excel, which is why I mentioned that the solution may be amazingly simple or more complex.
It is possible that they are output into a text file, into an Excel worksheet, on a web page, in a PDF, or on a display screen for the program itself. John doesn't indicate how the piece of equipment outputs the numbers, just that they are output as text. All of them start, however, by making sure that your destination cells are formatted as you want them formatted (which it seems you have done.) There are a few things you can try, from the amazingly simple to approaches more complex. This takes a lot of time! John wonders how he can paste data using the destination formatting. John then has to highlight the cell he just pasted, convert to a number, then format to three decimal places. Even though the destination cell in his worksheet is formatted as a number with three decimal places, that formatting is ignored when he pastes the data is always pasted as text. John has a piece of equipment that outputs numbers as text.