Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

up vote 4 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.