Paste the resulting values, not the formula with Paste Special in Excel

Occasionally, you may need to use a formula to change the way data appears. And, it is often handy to be able to extract the resulting value of a formula, but not the formula itself.

For example, you can use a formula to remove extra spaces from data. If you then wanted to split the resulting values across columns, you would need to extract the values because you can only split text across columns, not formulas.

To paste values

1. Select the cell(s) containing the formula(s).
2. Press CTRL+C to copy.
3. With the cell still selected, in the Edit menu, click Paste Special.
4. In the Paste Special dialog box, select Values, then click OK.

Note: You can paste the resulting values over the original data to “change” it, or you can select different destination cells to contain the plain text.

The steps above describe how to replace the formulas with corresponding values.

Trackback from your own site.