Witch is the longest text in a column

  • Thread starter Thread starter JanSolo
  • Start date Start date
J

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
 
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
 
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
 
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
 
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)
 
And to identify it. Again, CSE

=INDEX(A:A,MATCH(MAX(LEN(A1:A3500)),LEN(A1:A3500),0))
 
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
 
Back
Top