Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. XL2000: Calculation of Formula in Formula Bar Returns #VALUE!

XL2000: Calculation of Formula in Formula Bar Returns #VALUE!

View products that this article applies to.

This article was previously published under Q212013

↑ Back to the top

Symptoms

In Microsoft Excel 2000, when you press F9 to evaluate a formula in the formula bar, the formula may return #VALUE! even when the formula returns the correct result to the cell.

↑ Back to the top

Cause

This behavior occurs if the formula returns a string that contains more than 255 characters.

↑ Back to the top

More information

In Microsoft Excel, you can select all or part of a formula in the formula bar and press F9 to evaluate only the selected portion. If the selected portion returns a string that contains greater than 255 characters, it returns #VALUE!.

For example, if you type the following formula in a cell
=REPT("a",1000)
and you select the formula in the formula bar and press F9, the formula returns #VALUE! instead of a string that contains 1000 "a" characters as expected.

The following text functions may evaluate to #VALUE! if they return more than 255 characters: 
   CLEAN
   CONCATENATE
   LEFT
   LOWER
   MID
   PROPER
   REPLACE
   REPT
   RIGHT
   SUBSTITUTE
   TRIM
   UPPER

↑ Back to the top

Keywords: KB212013, kbprb

↑ Back to the top

Article Info
Article ID : 212013
Revision : 4
Created on : 10/8/2003
Published on : 10/8/2003
Exists online : False
Views : 274