Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 5 down vote accepted

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.

share|improve this answer
    
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
1  
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
1  
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .