Witch is the longest text in a column

[JanSolo]

Hello,

I have a few text columns with about 35000 rows every one, with names,
directions, comments, etc. and now I ask cell by cell witch is the longest
text, this take a lot of time and I want to know if there is another way to
do it.

Thanks

 

[Fred Smith]

Are you using the Len function?

Tell us how you are doing it now, then we can give you suggestions as to how
to improve it.

Regards,
Fred

 

[Bernie Deitrick]

Jan,

With your strings in column A, enter

=LEN(A1) into a cell in row 1, say, F1, and copy down 35000 rows to match
your strings.

Then use a formula like this, for

=INDEX(A:A,MATCH(MAX(F:F),F:F,FALSE))

to return the longest string from column A - witht he LEN formulas in F.


HTH,
Bernie
MS Excel MVP

 

[JP Ronse]

Jan,

With the data to check in column A:

somewhere in he workbook

=MATCH(MAX(LEN(A1:A3500));LEN(A1:A3500);0)

Enter this function as an array function: control-shift-enter


With kind regards,

JP

 

[Don Guillett]

This ARRAY formula (enter using ctrl+shift+enter) finds the longest LEN cell
=ADDRESS(MIN(IF(LEN(A1:A100)=MAX(LEN(A1:A100)),ROW(A1:A100)," ")),1)

 

[Don Guillett]

And to identify it. Again, CSE

=INDEX(A:A,MATCH(MAX(LEN(A1:A3500)),LEN(A1:A3500),0))

 

[JanSolo]

A lot of Thanks
I work with Excel in spanish, so I had to translate your formula, but it was
just I was looking for.

All with CSE as you say
=INDICE(H1:H33846;COINCIDIR(MAX(LARGO(H1:H33846));LARGO(H1:H33846);0)) tells
me the text
=LARGO(INDICE(H2:H33847;COINCIDIR(MAX(LARGO(H2:H33847));LARGO(H2:H33847);0)))
tell me its lenght

This is I DID to get the longest lenght of a text in a column:

MaxLen = 0
For Reg = 1 To Registros ' Registros is the amount of cells that contain
data
If Len(ActiveCell.Value) > MaxLen Then
MaxLen = Len(ActiveCell.Value)
End If
ActiveCell.Offset(1, 0).Select
Next Reg

In a range of 33850 cells it TOOK about 6 or 8 seconds, but now it's so
fast. So please, take a look at this code for numerical data and maybe you
can tell how to get it faster. Thank you.

When the column is numerical data I DO this (for now) to obtain Maxlen
(total lenght including decimal point if exist) and MaxDec (Max Decimals
present in the cells), ex: in 13445.36 MaxLen is 8 and MaxDec is 2,
in -13445.36 MaxLen is 9 and MaxDec is 2.

MaxLen = 0
MaxLenDeci = 0
For Reg = 1 To Registros
numstr = Trim(Str(ActiveCell.Value))
entero = Int(ActiveCell.Value)
If InStr(Str(ActiveCell.Value), ".") Then
Deci = Mid(numstr, InStr(Str(ActiveCell.Value), "."))
End If

If Len(numstr) > MaxLen Then
MaxLen = Len(numstr)
End If

If Deci > 0 And (Len(Deci) > MaxLenDeci) Then
MaxLenDeci = Len(Deci)
End If
ActiveCell.Offset(1, 0).Select
Next Reg