Maximum Length of Text in Column

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

Ken
Sat Dec 18 08:25:21 CST 2004

=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 :-)
----------------------------------------------------------------------------



"Joe" <Joe@xxxxxxxxxxxxxxxxxxx.com> wrote in message
news:OPAlatQ5EHA.4040@TK2MSFTNGP14.phx.gbl...
> 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
>
>

Joe
Sat Dec 18 08:32:09 CST 2004

Thanks

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:eY$$o1Q5EHA.2452@TK2MSFTNGP14.phx.gbl...
> =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 :-)
> --------------------------------------------------------------------------
--
>
>
>
> "Joe" <Joe@xxxxxxxxxxxxxxxxxxx.com> wrote in message
> news:OPAlatQ5EHA.4040@TK2MSFTNGP14.phx.gbl...
> > 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
> >
> >
>
>

Joe
Sat Dec 18 08:40:46 CST 2004

Ken

How can you macroise the array into the cell using VBA?

Thanks

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:eY$$o1Q5EHA.2452@TK2MSFTNGP14.phx.gbl...
> =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 :-)
> --------------------------------------------------------------------------
--
>
>
>
> "Joe" <Joe@xxxxxxxxxxxxxxxxxxx.com> wrote in message
> news:OPAlatQ5EHA.4040@TK2MSFTNGP14.phx.gbl...
> > 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
> >
> >
>
>

JE
Sat Dec 18 09:31:20 CST 2004

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))"


In article <u8MOs8Q5EHA.156@TK2MSFTNGP10.phx.gbl>,
"Joe" <Joe@xxxxxxxxxxxxxxxxxxx.com> wrote:

> How can you macroise the array into the cell using VBA?

Dave
Sat Dec 18 09:47:56 CST 2004

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

Joe wrote:
>
> Ken
>
> How can you macroise the array into the cell using VBA?
>
> Thanks
>
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
> news:eY$$o1Q5EHA.2452@TK2MSFTNGP14.phx.gbl...
> > =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 :-)
> > --------------------------------------------------------------------------
> --
> >
> >
> >
> > "Joe" <Joe@xxxxxxxxxxxxxxxxxxx.com> wrote in message
> > news:OPAlatQ5EHA.4040@TK2MSFTNGP14.phx.gbl...
> > > 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
> > >
> > >
> >
> >

--

Dave Peterson