Max Length of a field with Errors

[tyija1995]

Hi,

I want to find the maximum length of each field taking into account of errors that pop up.

For example I have data in cells A2:B938,

Column A is the ID number and Column B is a description,

I am using the following formula to get the max length:
{=MAX(LEN(A2:A938))} and similarly B2:B938 for column B
The first formula returns 3 (which is correct as the IDs go up to 952) but the 2nd formula returns #NAME ? as I have some errors in column B

Is there a formula I can use to bypass the errors and return the longest valid cell in column B? I have tried with AGGREGATE but can't quite get it to work...

Thanks.

 

[Scott T]

Try

=MAX(LEN(IFERROR(B2:B5,"")))

confirm with Ctr-Shift-Del

 

[tyija1995]

Hi Scott,

Thank you for the quick reply it has worked, much appreciated! :D

 

[Fluff]

How about this non array version
=AGGREGATE(14,6,(LEN(B2:B28)),1)

 

[tyija1995]

Thank you Fluff that has also worked! Very nice to see it done without an array formula, they always seem to trip me up as I am fairly new to learning them!

 

[Fluff]

You're welcome & thanks for the feedback