# Calculate the maximum number of character in a row for a column

I have a sheet in Excel that has several rows and 16 columns. I will transform this sheet in a Derby database to work with Java.

To create the Derby database I need to specify the size of each column, so it would be great to know the maximum numbers of characters in a single row for that column, to optimize my Derby database.

Example:

``````Col A │ Col B │ Col C
──────┼───────┼──────
a234  │ b2345 │ c2
a2    │ b23456│ c23
a23   │ b2    │ c234
``````

Result:

`````` 4    │   6   │  4
``````

I would like to do this without an extra column, but it seems impossible. Does anyone have a solution for that? Or what I want is unreal?

-

## 1 Answer

No extra column is needed, just one cell per column, within that column.

If the number of rows you have is set, you can use an array formula to determine the max length. The formula is `=MAX(LEN(A2:A100))`, confirmed with CtrlShiftEnter, where A2:A100 are your values. This tells Excel to evaluate the lenght (LEN) of all rows in the reference, then return the longest (MAX).

If your row count will change, you can use a Named Range to determine which cells will be evaluated, then use the defined Name in your Array formula.

-
It returned me an error, it seems that `LEN` doesn't work for a range of values. That's why I may need to create a new column for each column to know the length of each row. Got it? – Math Jun 25 '13 at 12:18
Wow, hold on!!! – Math Jun 25 '13 at 12:20
Dude.. What does Ctrl + Shift + Enter do?? It solved my problem :D, I've never heard about that before. Thank you!! – Math Jun 25 '13 at 12:22
Ctrl+Shift+Enter creates an Array Formula, which essentially tells Excel to evaluate all of the cells in the range, then return the requested value. For more info check out this site: cpearson.com/excel/arrayformulas.aspx. – dav Jun 25 '13 at 12:41
It shows as {=Max(Len(... )} But you cannot enter the curly brackets yourself. You have to press the Ctrl+Shift+Enter as @dav wrote and explained – pashute Oct 8 '15 at 20:17