Pasting and Matching Destination Formatting

Written by Allen Wyatt (last updated August 9, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365


3

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, 2021, 2024, and Excel in Microsoft 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. ...

MORE FROM ALLEN

Removing Duplicate Cells

If you need to often delete duplicate items from a list, then you'll love the macro presented in this tip. It makes quick ...

Discover More

Output Error when Pasting Right-to-Left Text

English is written left to right, but some languages are right to left. Mixing the two in a document can present a ...

Discover More

Returning Item Codes Instead of Item Names

The data validation capabilities of Excel are really handy when you want to limit what is put into a cell. However, you ...

Discover More

Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!

More ExcelTips (ribbon)

Selecting Formulas

Want to select only the formulas in your worksheet? It's easy to do using the Go To Special dialog box.

Discover More

Controlling the Automatic Copying of Formulas

When you add a new row of data to the bottom of the data of a worksheet, Excel may (or may not) copy formulas downward to ...

Discover More

Copying from the Task Bar

When you select cells in a worksheet, there is a good chance that if you glance at the Task Bar, you'll see some ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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}] (all 7 characters, in the sequence shown) 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 four less than 4?

2025-08-10 10:32:20

J. Woolley

@Alex Blakenburg
Good catch.
An Excel cell can contain four basic data types: text, number, logical, and error.
When VBA assigns numeric text to a cell it is saved as a number (Double). Here's an example (assuming default thousands separator is comma): "1,234"
When VBA assigns logical text to a cell it is saved as logical (TRUE/FALSE). Here's an example (notice case is ignored): "true"
When VBA assigns error text to a cell it is saved as an error value. Here's an example (notice case is ignored): "#div/0!"
When VBA assigns any other text to a cell it is saved as text.
When VBA assigns a number, logical, or error to a cell it is saved as that type.


2025-08-09 18:23:03

Leslie

An additional issue arises when the decimal marker is a centred dot - which is easily mistaken for the decimal point.

No amount of manipulation will convert this text to numeric. Use Ctrl+H to substitute the centred dot with a decimal point.


2025-08-09 09:48:48

Alex Blakenburg

In the above macro the line "c.Value = CSng(c.Value)" is introducing a floating point error that wasn't there before.
You don't get that if you just do this:

Sub DoConvert()
Dim c As Range

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


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
Subscribe

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.