Excel

[foxy]

How to find the cell with the most alpha/numeric characters within th
same column? See attached file for example. Column C has the wides
column width. Is there a way to find the cell with the longest line o
data in column C without manually paging down and looking

+----------------------------------------------------------------
| Attachment filename: example for excel.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=369068
+----------------------------------------------------------------

 

[Chip Pearson]

You can do it with VBA code. For example:

Dim Rng As Range
Dim MaxLen As Integer
Dim MaxCell As Range
For Each Rng In Application.Intersect( _
ActiveSheet.UsedRange, ActiveSheet.Columns(3))
If Len(Rng.Text) > MaxLen Then
Set MaxCell = Rng
MaxLen = Len(Rng.Text)
End If
Next Rng
MsgBox "The longest cell is: " & MaxCell.Address & vbCrLf & _
"It contains the text: " & MaxCell.Text & vbCrLf & _
"The text length is: " & MaxLen



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Dave Peterson]

You could use a formula, too:

This formula gave me the value of the first cell with the longest length:
=INDEX(C1:C100,MIN(IF(LEN(C1:C100)=MAX(LEN(C1:C100)),ROW(C1:C100))))

And this formula gave me the first row of the value with the longest length:
=MIN(IF(LEN(C1:C100)=MAX(LEN(C1:C100)),ROW(C1:C100)))

For both these formula:
Instead of just hitting enter, I hit ctrl-shift-enter. This is an array
formula that will fill the selected cells with its results. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

 

[Dave Peterson]

And slightly shorter:

=INDEX(C1:C100,MATCH(MAX(LEN(C1:C100)),LEN(C1:C100),0))
and
=MATCH(MAX(LEN(C1:C100)),LEN(C1:C100),0)

Again, both are array formulas.

You could use a formula, too:

This formula gave me the value of the first cell with the longest length:
=INDEX(C1:C100,MIN(IF(LEN(C1:C100)=MAX(LEN(C1:C100)),ROW(C1:C100))))

And this formula gave me the first row of the value with the longest length:
=MIN(IF(LEN(C1:C100)=MAX(LEN(C1:C100)),ROW(C1:C100)))

For both these formula:
Instead of just hitting enter, I hit ctrl-shift-enter. This is an array
formula that will fill the selected cells with its results. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

 

[foxy]

This is not working for me. It could very well be me or maybe I didn'
give enough info. I am new to excel. I have checked my synta
carefully. When I open a file in excel containing addresses I firs
auto fit all columns, then determine which column has the widest width
Within that column I have to find the line (row) with the longest dat
in it. Currently I have to manually page down and look through al
lines (rows) of data, which can be 28,000 lines, to find the longes
line of data. Which cell should I be entering the formula you gave m
in reference to the example worksheet I attached earlier? If I ente
it in C1 it wipes out my data in the entire column and returns zeroes.
It should be telling me that "4345 Quincy Terrace Park" is the longes
line

 

[Paul B]

Foxy, you asked to check for the "longest line of data in column C", that is
what chip gave you, here is his code modified to check the whol sheet

Sub Find_Longest()
'modified from Chip Pearson's to check the whole sheet
Dim Rng As Range
Dim MaxLen As Integer
Dim MaxCell As Range
For Each Rng In ActiveSheet.UsedRange
If Len(Rng.Text) > MaxLen Then
Set MaxCell = Rng
MaxLen = Len(Rng.Text)
End If
Next Rng
MsgBox "The longest cell is: " & MaxCell.Address & vbCrLf & _
"It contains the text: " & MaxCell.Text & vbCrLf & _
"The text length is: " & MaxLen
End Sub

To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is to the left of the "File" menu this will open
the VBA editor, in the left hand window click on your workbook name, go to
insert, module, and paste the code in the window that opens on the right
hand side, press Alt and Q to close this window and go back to your workbook
and press alt and F8, this will bring up a box to pick the Macro from, click
on the Macro named Find_Longest to run it. If you are using excel 2000 or
newer you may have to change the macro security settings to get the macro to
run.
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **