Maximum Length of Text in Column

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Is there a function that will return the maximum lengh of a text in a
column. Eg.

a3: Text1
a4: Text1Text2
a5: Text1Text2Text3

Thus a formula that returns 15 a the maximum length.

Thanks in advance
 
Thanks

Ken Wright said:
=MAX(LEN(A1:A100)) array entered using CTRL+SHIFT+ENTER

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission :-)
-------------------------------------------------------------------------- --
Click to expand...
 
I'm pretty shaky on what "macroise the array into the cell" could mean,
but if you mean to use a macro to enter the array-formula Ken gave you
into a cell, take a look at the FormulaArray property in XL/VBA Help.
One way:


Range("B1").FormulaArray = "=MAX(LEN(R1C1:R100C1))"
 
You could loop through each of the cells looking for a longer string or you
could ask excel to evaluate your formula:

One way to ask:

Option Explicit
Sub testme()

Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

MsgBox Application.Evaluate("max(len(" _
& myRng.Address(external:=True) & "))")

End Sub
 
Back
Top