Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

Past Tip of the Day

 

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.

By Bill Jelen on 26-Oct-2001

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.