I need help in using MAX(LENGTH) Excel formula to find the minimum length needed to accommodate my existing data. I have a sheet of more than 200k rows to import to a newly designed database.

The sheet contains GDPR-sensitive information but I can show here just a few cells with numeric identifier that do not cause harm

I need to find the MAX LENGTH of every cell in each column separately.

I tried to start with B column. I put myself into a blank cell, and typed (this Excel uses Mr. Dante Alighier's native language)

=MAX(LUNGHEZZA(B:B))
=MAX(LEN(B:B))

The returned value was 7. But please look at cell B9 (0304000790): it has 10 characters.

What is wrong in my formula computation? I have looked around the Internet but the formula recommended by others is the same I'm trying here.

1 Answer

LEN is expected to work on one cell, not many.

In order to make it work on many cells, you must enter it as an array formula. Enter the formula not using the Enter key, but a combination of Ctrl+Shift+Enter. This "encapsulates" the formula in curly braces {=....} .

Note that array formulas are not clever enough not to include empty cells at the bottom of your file, if you passed the whole column to the formula. In this case it may be very slow, because it will loop and calculate lengths of every cell, even empty and not used ones at the bottom. You may want to limit your passed column size, e.g. =MAX(LEN($B$2:$B$200000)) .

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy