How do I get the longest text of a row in some column in the middle of an Excel file? Formulas or VBA are okay

Question

I have an excel file in the column somewhere in the middle of which (yellow selected row for example) I need to find the longest text of that row, and do that for each row in that column.

I obviously get a circle reference if I simply select the whole row as the range, and what I tried with VBA making two ranges out of that gets over-complicated quickly, making me think there must be an easier way, potentially even using a formula.

I have to follow the following rules due to requirements for the file:

• I can only use that column (yellow)
• I don't know per se how long the row is going to go on, something I can easily check via VBA
• the leftmost column has to be excluded

Is there an easy way? Any ideas?

I tried simply selecting the row, obviously leads to a circle reference. I've tried defining arrays via formula to include the two ranges, that didn't quite work out. I've begun writing VBA code, but trying to combine two Ranges of that row, excluding a cell in-between is where it falls flat.

Answer 1

Here is one option:

Formula in E2:

=BYROW(2:7,LAMBDA(x,LET(y,DROP(FILTER(x,COLUMN(x)<>COLUMN()),,1),@SORTBY(y,LEN(y),-1))))

Answer 2

The "brute force" technique is to access an additional dimension by inserting a new Sheet.
In that new Sheet2, in B2, have =LEN(Sheet1!B2) and drag across and down.
That has given you an easier point from which to find what it is that you want.
Even if the data covered every single cell in Sheet1, and so in Sheet2, you could add Sheet3, and in B1 put E.g. =MAX(Sheet2!B:B) and in B2 =IF(Sheet2!B2=Sheet3!$B$1,ROW(),"")
Then on Sheet4, in B2, =OFFSET(Sheet1!B1,SUM(Sheet3!B2:B1048576)-1,0)

In practice you probably do have some spare rows/columns (can you add a row at the top of the source sheet?) so you can probably put the answer on Sheet3.
The key is to separate the different steps: finding the length; finding the largest length, finding in which cell that occurs, and accessing the value in that cell.