Pasting and Matching Destination Formatting

John has a piece of equipment that outputs numbers as text. 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 then has to highlight the cell he just pasted, convert to a number, then format to three decimal places. This takes a lot of time! John wonders how he can paste data using the destination formatting.

There are a few things you can try, from the amazingly simple to approaches more complex. 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.)

John doesn't indicate how the piece of equipment outputs the numbers, just that they are output as text. 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. 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.

Regardless of how the numbers are output, select them and press Ctrl+C. This copies the numbers to the Clipboard, and you can switch over to Excel. Select the cell where you want the information pasted, but don't click Ctrl+V. Instead, display the Home tab of the ribbon. At the left side you'll see the Paste tool; you want to click the down arrow at the right of this tool. On the resulting palette of options or resulting Context menu, you want to choose Paste Values, Keep Text Only, or Match Destination Formatting. (See Figure 1.)

Figure 1. The Paste tool's Context menu.

Any of these three options is designed to do essentially the same thing—simply paste values without any formatting from the source. (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.

If your data is now in the worksheet using the desired destination formatting, you are good to go. It is possible, though, that this simple approach may not work as you expect. (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. (See Figure 2.)

Figure 2. The Paste Special dialog box.

In this case, however, you want to select not only the Values radio button, but also the Add radio button. 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.

Not even this approach has worked for some of my pasting operations. For instance, I may end up copying from the source program multiple values that should end up in multiple cells in the Excel worksheet. 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. 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. 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.

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. That way you won't mess up your eventual destination worksheet. You also can copy the massaged data and paste it in that destination using the Paste Special dialog box using the techniques already described.

I should mention another approach you can use if you are pasting values that will appear in a single cell. Once you select the destination cell, you should paste into the Formula bar instead of into the cell itself. In other words, select the cell and then click once in the Formula bar. There you can press Ctrl+V to paste the value and then press Enter. The formatting of the cell should not be disturbed in the least.

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. Here's a quick one you can use:

Sub DoConvert()
    Dim c As Range

    For Each c In Selection
        If IsNumeric(c) And Not IsEmpty(c) Then
            c.Value = CSng(c.Value)
            c.NumberFormat = "0.000"
        End If
    Next c
End Sub

Just select the cells to affect (those that are wrongly formatted as text after doing the pasting) and then run the macro. It applies the desired formatting to the cells after converting the values in the cells into actual numeric values. You should note that it will not do any multi-cell splitting, the type I previously described where the values copied from the source included spaces between values rather than tabs.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5970) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.