You are currently offline, waiting for your internet to reconnect

#VALUE! Error When Concatenating Cells

This article was previously published under Q94202
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
SYMPTOMS
In Microsoft Excel, when you concatenate cells, you receive a #VALUE! errorif the Transition Formula Evaluation (TFE) option in version 5.0 or theAlternate Expression Evaluation (AEE) option in version 4.0 is enabled andone of the concatenated cells is blank or contains a number.

This behavior also occurs with the INDIRECT function when you useconcatenated cells. For example, the formula

=INDIRECT("A"&B1)

returns A6 if B1 has a value of six and TFE or AEE is not selected. Theformula returns the #VALUE! error if TFE or AEE is selected.
WORKAROUND
To avoid receiving the #VALUE! error, do either of the following.

Disable the TFE or AEE option.

NOTE: Make a backup copy of your worksheet. (If you disable alternateexpression evaluation, the values calculated on your worksheet maychange. If you have a backup copy of your worksheet, you cancompare the earlier version of your worksheet with the updatedversion and make sure that your calculations are correct.)

Microsoft Excel 98 Macintosh Edition

To disable the TFE or AEE option, follow these steps:
  1. On the Tools Menu, click Preferences and click the Transition tab.
  2. Under Sheet Options, clear the Transition Formula Evaluation check
box and click OK.

Microsoft Excel Version 5.0

To disable the TFE or AEE option, follow these steps:
  1. On the Tools menu, click Options and click the Transition tab.
  2. Under Sheet Options, clear the Transition Formula Evaluation check box and click OK.

Microsoft Excel Version 4.0

To disable the TFE or AEE option, follow these steps:
  1. On the Options menu, click Calculation.
  2. Under Sheet Options, clear the Alternate Expression Evaluation check box and click OK.
-OR-

Use the TEXT Function

To concatenate cells while TFE or AEE is enabled, use the TEXTfunction in any concatenation formulas with cells that contain numericvalues or are blank, as in the following example:

=A1&TEXT(A2,IF(A2="","","0"))

The above formula combines the contents of cell A1 with the contentsof cell A2, and uses the TEXT function to ensure that the contentsof cell A2 are interpreted as text. The IF statement ensures that thecell is formatted correctly based on whether cell A2 is blank orcontains a number.
MORE INFORMATION
If Transition Formula Evaluation or Alternate Expression Evaluation isselected, the worksheet is calculated based on the Lotus 1-2-3 rules forevaluating expressions instead of the Microsoft Excel rules. This option isautomatically selected when you open a Lotus 1-2-3 worksheet in MicrosoftExcel.
REFERENCES
"User's Guide 1," version 4.0, page 57

"User's Guide 2," version 4.0, page 237

"Function Reference," version 4.0, page 431
indirect Excel
Properties

Article ID: 94202 - Last Review: 08/15/2005 22:34:50 - Revision: 1.3

  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 98 for Macintosh
  • kbprb KB94202
Feedback