XL: Pasting Text in Center Across Cells Format Returns 0

ID: Q98632

The information in this article applies to:


SUMMARY

In Microsoft Excel, when you format a cell with the Center Across Selection option, the contents of one cell will be centered across multiple cells. In this format, although the data appears to be contained in several cells, the actual data can only be accessed from the original cell.

Although the information appears to span several cells, you can only retrieve information from the original cell (the cell to which you applied the Center Across Selection format). For this reason, operations, such as the following, will not retrieve data from the cells that appear filled (because of the formatting change):

- In the case of linking, the link will return a 0 (zero). This is expected behavior when you link to an empty cell.

-and-

- If you attempt to use the Paste Link command (on the Edit menu) to paste an area in which text has been formatted with the Center Across Selection option, the array returned to the destination sheet may look different than anticipated.

MORE INFORMATION

The array returned in the destination worksheet will display the text as if the Center Across Selection option had been selected, but the text will be truncated in the first cell of the centered selection. A 0 (zero) is displayed in the other cells of the centered selection.

This is expected behavior because of the way Microsoft Excel handles linking. When you link a document by choosing Paste Link from the Edit menu, Microsoft Excel does not copy formatting and automatically puts a 0 in cells that are blank in the original region.

To display the information in the linked worksheet as it appears in the source worksheet, do the following:

  1. Select only the individual cell that contains the aligned text on the original (source) worksheet. Do not select the other cells that the text is centered across.


  2. On the Edit menu, click Copy.


  3. Activate the destination worksheet.


  4. Select the destination cell.


  5. On the Edit menu, click Paste Link.


The information is pasted correctly, but does not take on the alignment of the source cell. To align the information, do the following:

Excel 5.0

  1. Select the cells that you want to center the text across.


  2. On the Format menu, click Cells.


  3. Click the Alignment tab and select the Center across selection option in the Horizontal dialog box.




Excel 4.0, 4.0a

  1. Select the cells that you want to center the text across.


  2. On the Format menu, click Alignment.


  3. Under Horizontal, select the Center Across Selection option.


  4. Click OK.


To see an example of this behavior, do the following:

  1. In cell A1, type "This is a test" (without the quotation marks).


  2. Select cells A1 through C1.


  3. On the Format menu, click Alignment.


  4. Under Horizontal, select the Center Across Selection option.


  5. Click OK.


  6. On the Edit menu, click Copy.


  7. Activate a new worksheet.


  8. Select cell A1.


  9. On the Edit menu, click Paste Link.


The result will be text truncated in cell A1, and the value 0 (zero) displayed in cells B1 and C1.

REFERENCES

"User's Guide 1," version 4.0, page 228

Additional query words: 


Keywords          : xlformat 
Version           : WINDOWS: 4.0, 4.0a, 5.0; MACINTOSH: 4.0, 5.0
Platform          : MACINTOSH WINDOWS 
Issue type        : kbprb

Last Reviewed: April 6, 1999