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 superformulas 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 26Oct2001
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.
