Excel Max Length of each column data

5
votes

Simple one for someone hopefully. I'm trying to get the max length of each column in an excel sheet but it doesn't seem to be working now. I was using =MAX(LEN(A2:A200)) for example and because MAX is not an "official" formula but is still there you seem to have to press CTRL + ALT + SHIFT once you have finished typing it.

When I do this it puts curly brackets round the whole formula like this {=MAX(LEN(A2:A200))} but if I click into the cell they disappear. the length of the first column will appear but then if I autofill to the next column it shows same length of column data from the first column rather than calculating the next column.

Perhaps not a straight up programming question but scratching my head why this is not working now. I'm trying to show max length of each column above header column in new row.

Thanks

Andrew

4
excelmaxlength

4 Answers

8
votes

In A1 enter the array formula:

=MAX(LEN(A2:A200))

then stop clicking.

Move to A1 with the ARROW keys. Touch Ctrl+c. Then paste to B2 through G2 using Ctrl+v:

enter image description here

This will put the array formula in the destination cells.

7
votes

Use Gary's formula and instead of ENTER, do CTRL-SHIFT-ENTER

1
votes

When I do this it puts curly brackets round the whole formula like this {=MAX(LEN(A2:A200))} but if I click into the cell they disappear. the length of the first column will appear but then if I autofill to the next column it shows same length of column data from the first column rather than calculating the next column.

It is right to use formulas with brackets (they are called Matrix formulas). The brackets disappears because when you edit the content of the cell you have to push Ctrl + Shift + Enter and not only Enter like normal formulas.

1
votes

Let's assume all your data is in Sheet1 starting at cell A1.

  1. Create Sheet2
  2. Enter the =LEN(Sheet1!A1) formula in cell A1 on Sheet2
  3. Pull Sheet2's A1 cell down/over to the right to cover the populated range of Sheet1 (to copy the formula to every cell of the range in scope) - this will give you the length of every cell in scope
  4. Enter the =MAX(A1:A10) formula on Sheet2 in the first column below the last line (A10 is assumed to be the last line of the first column in this example - so the formula goes in A11)
  5. Pull Sheet2's A11 cell over to the right till the last column to get the MAX length of values for each column