Field lengths in Excel

[Petzl]

I work regularly with importing fields from Excel where I clean to a database. I need to know the field lengths to determine the field size to create in the database. At present it takes time to go through each column, insert a column to the right, put a =len(A1) formula in and then sort. This gets repeated for the total number of columns.

Is there an easier way where I could just enter a formula at the top of a column and it will give me the greatest character length in that column?

Cheers!

 

[cheerio]

where you want the answer key

=MAX(LEN(A3:A6))

where A3:A6 is your data range.

However, instead of pressing ENTER you press CYTR+SHIFT+ENTER

This creates an array formula.
In the formula bar you will see
{=MAX(LEN(A3:A6))}

The curly brackets indicate the formula is an array formula. You cannot key them yourself.

Ken

 

[cheerio]

Sorry there was a typo - I meant

CTRL+SHIFT+ENTER

Ken

 

[Petzl]

Unbelievably quick Ken!!!

This will save me heaps of time!!

Thanks very, very much!!!

Nicho