Finding cell in Row with most data and entering CHAR(10)

[dfolzenlogen]

I have a spreadsheet with 10 columns containing hyperlinks, dates and wrapped text. The text cells vary in length. I need to determine which of the text cells contains the most data and then programmatically enter ALT+ENTER (CHAR(10)). I don't have a clue as to how to start. Does anyone have any suggestions?

Thanks.

 

[James006]

Hello,

Say in cell L1 ... you can test the Array Formula ... and copy it down ...

=INDEX(A1:J1,MATCH(MAX(LEN(A1:J1)),LEN(A1:J1),0))

Hope this will help

 

[dfolzenlogen]

Hello,

Say in cell L1 ... you can test the Array Formula ... and copy it down ...

=INDEX(A1:J1,MATCH(MAX(LEN(A1:J1)),LEN(A1:J1),0))

Hope this will help

Thank you! I will give it a try!

 

[James006]

Forgot to add instead of the Enter key ... an Array Formula requires to use simultaneously the three keys Ctrl+Shift+Enter ... :wink:

 

[wideboydixon]

Programmatically suggests you want to use VBA. Something like this is your data is in columns A:J

Public Sub InsertChar10()

Dim lastRow As Long
Dim thisRow As Long
Dim thisCol As Long
Dim longestText As Long
Dim longestCol As Long

' Find the last row
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

' Loop through all rows
For thisRow = 1 To lastRow
    ' Assume for now that the longest cell value is in column A
    longestText = Len(Cells(thisRow, 1).Value)
    longestCol = 1
    
    ' Loop through columns B to J
    For thisCol = 2 To 10
        ' If the value in this column is longer than the current longest value
        If Len(Cells(thisRow, thisCol).Value) > longestText Then
            ' Remember this column
            longestCol = thisCol
            
            ' Remember the length of the text in this column
            longestText = Len(Cells(thisRow, thisCol).Value)
        End If
    Next thisCol
    
    ' Now update the longest column in this row by appending vblf = Chr(10)
    Cells(thisRow, longestCol).Value = Cells(thisRow, longestCol).Value & vbLf
Next thisRow

End Sub

WBD

 

[Joyner]

Hello and welcome to the board. When you say : "...determine which of the text cells contains the most data" are you specifically looking for just one cells with the most data/text length, or for cells over a certain length? And when you say: "...then programmatically enter ALT+ENTER (CHAR(10)). " where/how do you want to enter the return character?