Excel copy Custom Format tutorial

Take EXCEL2007 as an example to copy the custom format!

We know that custom formatting is a common feature in Excel that changes the format of the data display without changing the original data, for example, the cell A1 in Figure 2.14 has a value of "150" but is displayed as "150 yuan." After you copy A1 cells, you cannot paste the displayed content (that is, "150 yuan") into other cells, either by using a normal paste method or by pasting.

That is conducive to statistical operations, but also easy to print and other output.

If we need to replicate what the custom format shows, rather than the data itself, we can take advantage of the following three methods:

1, insert directly into the cell. First select the range of data you want to copy, then press CTRL + two consecutive times to copy and pull the clipboard, double-click the target cell or press F2 to enter the cell edit state, and then click the object you want to paste in the Clipboard to paste into the cell.

The advantage and disadvantage of this approach is that if you have more than one row of data, you merge it into the same cell.

2, use Notepad. First copy the contents of the data range, paste it into Notepad (in a text file), and then copy the content that you just pasted in Notepad and paste in the target area of Excel. Note that if the displayed value can be automatically converted to data, the cell format of the target area needs to be set before pasting as text!

The disadvantage of this approach is that you need to borrow other tool software.

3, the selective pasting. First select the range of data you want to replicate, then press CTRL + C two consecutive times to copy and pull up the Clipboard, then select the target area, then click on the Clipboard to paste the object to paste (important), and finally use the choice of paste-text, you can paste the data we need to the target area. Similarly, if the displayed value can be automatically converted to data, you need to set the cell format of the target area to text before the last selection is pasted!

The following is a method that copies the contents of a custom format using the Clipboard.

Method 1: After you copy the cell, select the target cell, click the formula bar (or double-click the target cell), and then click the object you want to paste in the Clipboard.

Method 2: After the cell is copied, select the Target cell, click the object that you want to paste in the Clipboard, paste the copied object into the target cell, right-click "Paste" in the target cell, and select paste mode "Text" in the pop-up Paste Selective dialog box, as shown in Figure 2.14.

Tip: You can copy A1 cells and paste them into Notepad or Word documents, so you can copy the displayed content.

Using this technique, you can convert a numeric date such as "20061023, 20070103" to a date string such as October 23, 2006, January 3, 2007, and so on, by first setting the format of the cell to a custom format of "0000-00-00", and using the previous clipboard tips, Copy the display values to the original cell, pasting "20061023, 20070103" into the date value "39013, 39085", and then formatting the date format that you want:

Then use the Clipboard to copy the display value to a string such as "October 23, 2006, January 3, 2007".