Pasting and Matching Destination Formatting

by Allen Wyatt
(last updated March 2, 2019)

2

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 Office 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

Relative VBA Selections

Need to select a cell using a macro? Need that selection to be relative to the cell you currently have selected? Here are ...

Discover More

Displaying Row and Column Labels

When you create a worksheet, it is common to place headings at the top of each column and the left of each row so you can ...

Discover More

Adding Common Line Spacing Options

Sometimes the options presented by Word's tools don't exactly meet our formatting needs. Rather than changing the tools ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Merging Cells to a Single Sum

One way to make your worksheets less complex is to get rid of detail and keep only the summary of that detail. Here's how ...

Discover More

Limiting Input to a Format

When setting up a worksheet for others to use, you might want to make some limitations on what can be entered in certain ...

Discover More

Getting Rid of Non-Printing Characters Intelligently

Is your worksheet, imported from an external source, plagued by non-printing characters that show up like small boxes ...

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}] 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 8 + 7?

2019-03-05 04:26:47

Mike

It's difficult to test a theory without having a sample of the actual data, but pasting into Notepad first and then re-selecting, copying and pasting into excel seems to work for all the tests I have tried.


2019-03-04 12:59:56

Roy

It literally cannot "be text" unless it uses a number set (what we realize are numerals) outside the normal keyboard characters. Else that, when any other program in the world looks at it, it is free to choose how to deal with the numerals.

So it must be CONVINCING Excel to treat them as text which implies it is ouputting them as Excel formatted material with "text" for the number format. That would convince Excel to handle them as text.

The reason Excel protections don't have a chance of working if one wishes to not have them work is that any other program can simply ignore the Excel formatting that protects things in their various ways. Formula is hidden? Well, our spreadsheet simply ignore s that and your formula is there for the world to see. Etc.

Since Excel is being convinced to treat it as text, one only really has to overcome that convincing. Open the material, as with the macro included here, and write it to the data section of the spreadsheet and that's all that you need. Slightly more complicated would be the multiple data items per cell, but John (Asker) did not mention any difficulties like that. (Admittedly, that could have been to avoid material for solving that if he is already very familiar with how to do so. But it is equally possible that what he really meant was the machine does that and by intentionally mis-describing the situation, he might get a solution that avoided having to do all the things he's very familiar with. Strategy...)

But again, if as described, the output must be Excel formatted and therefore straightforward to solve with the macro approach due to there not really being any other possibilities.

One wishes one could say the machine manufacturer that was visionary enough to set it up would neither have been so obtuse afterwards to force only text formatting of the output and so there must be a setting that can be found that formats the output as numbers instead, nor mendacious enough to set it up so it could only be text because he was simply ticking a feature box, not understanding what the feature needed, and didn't care past the tick mark. Sadly, either is possible.

But John (Asker) should look at the material available about his machine, any manuals he has or on the internet or even with the manufacturer though one suspects that last is not available (if not the original purchaser of a machine, manufacturers tend to regard you as a thief (you stole a NEW machine sale from them by buying a used machine is the idea) instead of a new revenue source, and he likely would find the setting that corrects the output to be formatted as numbers. That would beat an Excel fix.

The other things presented above are nice background but only the machine presenting Excel-formatted and Excel-convention-named material is really possible here.


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.