MrExcel.com - Your One Stop for Excel Tips & Solutions

Find the Longest Content in Cells

October 26, 2001 - by

Ketan writes:

I want to write an formula which calculates the max of length of the column values. For e.g. if we have a column

a
aa
aaaa
aa
aaaaaa

Then I want to insert a row at the top and formula should return the max of the length of the column values. In this case 5.

I know that this can be done by inserting a column and typing the formula =len(a1) then writing max(b1:b5), but when I have a worksheet containing large no. of columns, a single formula to calculate like this will be very helpful.

Yes, Ketan this formula would be very helpful. There are a whole class of super-formulas in Excel that can solve problems like this one in a single cell. Veteran MrExcel readers know this is my very favorite tip. It will move you to the front of the pack, ahead of 95% of other Excel users. You can read the complete details about CSE Formulas. Assuming your column of data is in A2:A2000, you would use this formula:

=MAX(LEN(A2:A2000))

You must hold down Ctrl, Shift and then hit Enter. I call these CSE formulas as a reminder that the magic key sequence is Ctrl + Shift while hitting Enter.

These formulas are very powerful and allow you to replace an entire column of formulas with a single formula.