IFERROR reads a long text as Error when Input is a Range

Frequent Contributor

For some reason, IFERROR function seems to be reading (registering) a long text as an Error when the input is a range of cells. It works fine if the input is a single cell. I don't know if it is due to some limitations with length of a string which an array member can hold or some bug with the function. Currently managing with workaround of conditional formatting to mask the error, but pls. share a better technical solution to this!

The demo file is attached.

 

Update : The preview of attached excel file works fine, but on my desktops at home and work it is not working fine!

Screenshot follows.

 

Desktops  :  Not working fine (Registers long text as Error)

Excel for Web : Not working fine (Registers long text as Error)

Document Preview feature of this post : Working fine!   (Reading text as text)

 

Is it something to do with operating system?

i have 64 bit Windows 10 at home and work.

 

 

 

amit_bhola_0-1627108994771.png

 

amit_bhola_0-1627110117958.png

 

 

7 Replies

@amit_bhola 

The limit is 255 characters. 255 is the largest number that fits into 1 byte (8 bits); it is a limit that occurs in many places in Excel.

You already know how to avoid it: use =IFERROR(B3,"Error!!") and fill down.

@amit_bhola 

(I didn't understand what you exactly mean with Document preview)

 

IMHO it's MSFT's call to say bug/not bug. Here is a possible explaination though:

The max. length of a text value in a formula is 255 char. Enter the following in a cell (len = 255):

 

="aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"

 

=> OK

 

Try adding 1 char. to the above formula:

 

Formula.png

 

Do the same with an array. In another cell enter (len = 255):

 

={"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"}

 

=> OK

 

Try adding 1 char to the above array:

 

Array.png

 

In the file you shared: Select D3 > Edit the formula in the formula bar > Select B3:B4 only > F9:

 

B3B4.png

 

Oops, coming late

@amit_bhola 

It works on Excel which doesn't support dynamic arrays, and doesn't work on Excel with dynamic arrays.

It looks like IFERROR() returns an error if element of the array is more than 255 characters length. If you work with one element (or, the same, without dynamic arrays) it takes only one element. If you convert the range into array another logic works, function consider elements with <= 255 characters only.

 

You may test

=IFERROR(LEFT(B3:B4,255),"error") - works

=IFERROR(LEFT(B3:B4,256),"error") - doesn't work

@amit_bhola 

Why use IFERROR() in the first place? There is no error in a text string.

Just =B3:B4 works fine.

 

Thanks all, pls. allow me to reply you all in a single post,

 

@Sergei Baklan  Thanks for the suggestion, it seems a better way of trapping the IFERROR error. But strangely though, the other functions taking long text as input/output are working fine, e.g. i checked =UPPER(B3:B4) and it worked perfectly OK. The 255 array element length limit behaviour seems to be specific to IFERROR only.

amit_bhola_0-1627138709824.png

 


@L z.  Thanks, i confirm the D3 #VALUE! test. It shows the limit to be of 255 for IFERROR reading an array/range. But as i write above, the limit is not there for other functions like UPPER.

As for whether it is a bug or not, a formula behaving differently as per excel version, or not giving its output as an error or ##### string in case limit is reached and instead gives an unexpected non-intuitive output is certainly something which needs some attention, at least documentation. But that's for MS to do.

By Preview file, i meant clicking the file name in the original post instead of downloading the file. You may confirm that it works fine in the preview mode, by clicking the yellow highlighted in the original post.

amit_bhola_1-1627138895597.png


@Hans Vogelaar @Detlef Lewin  Thanks, but i cannot drag fill or cannot avoid using IFERROR because my actual application of this function involves outputting an array. In this post i simplified the question leaving out that detail.

@amit_bhola 

Excel has 30+ years history. Different functions was written by different people, even by different generations of people. Assumptions in implementations were taken long before dynamic arrays appeared, even if internally arrays always exist. Thus that could be different behaviour in similar situations. Rewrite all functions to meet all new realities that means to stop further Excel development. That is done for most critical cases only.