Finding the maximum length string in a range of cells can be handy especially if you are using the max length for another purpose. The following custom function will find the maximum cell length in a range.
Function LongString(rng)
LongString = Evaluate("Max(len(" & rng.Address & "))")
End Function
The custom function is called as follows:
= LongString(A1:A10)
So in a range of cells from A1:A10 the custom function will work out the cell with the maximum length and return that number.
A custom function is a bit of overkill if you want to get into some array formula. The following:
{=MAX(LEN(A1:A10))}
The formula is confirmed with Ctrl Shift & Enter.
If the field name with the most characters is what you are after then incorporating an INDEX and MATCH formula is a way to get a solution.
{=INDEX(A1:A10,MATCH(MAX(LEN(A1:A10)),LEN(A1:A10),0))}
Once again the formula is confirmed with Ctrl Shift & Enter.
The formula is in 4 parts, the INDEX formula is driving the result
INDEX(Result Range, Row Number, Column Number)
Result Range = A1:A10
ROW Number
The MATCH formula decides the Row number in our case, so the MATCH function is
MATCH(Match Criteria, MATCH RANGE, MATCH TYPE)
The Match criteria = maximum length of the cell.
Match Array or Range is the range where that max is stored.
Match Type = In this case it is an Exact match which is 0 or FALSE (they mean the same thing but 0 is shorter to type.
Column Number - in this case it is not required.
So with CTRL SHIFT and ENTER at the same time the array formula is formed and a result is achieved..
