What is ROW(INDIRECT("1:40")) doing? (array formula)

[Guest]

I have a several _array_ formulas (written by someone else) that are breaking
up a long text string into smaller parts (without splitting a word in half).
I'm trying to usnerstand what the function is actually doing.
The first function (which pulls out the first 40 characters (rounded down to
the nearest full word) is the following:

=IF(LEN(A11)<=40,A11,LEFT(TRIM(A11),MAX((MID(TRIM(A11),ROW(INDIRECT("1:40")),1)=" ")*ROW(INDIRECT("1:40")))))

I thought I knew how both the "row" function and the "indirect" functions
worked, but obviuosly I don't... ahhhhh

 

[Biff]

Hi!

ROW(INDIRECT("1:40"))

This is just a means of stepping through the text being processed.

INDIRECT locks the the range 1:40 so that it can't change. ROW simply passes
the range argument 1:40 to whatever function or operation it's being used
for.

If A11 >40 then ROW(INDIRECT("1:40")) steps through the first 40 characters
of text like this:

MID(A1,1,1)
MID(A1,2,1)
MID(A1,3,1)
MID(A1,4,1)
...
MID(A1,40,1)

MID(TRIM(A11),ROW(INDIRECT("1:40")),1)=" "

Returns an array of TRUE's and FALSE's. TRUE is returned where there are
spaces in the text string. That array is then multipled by the second call
to ROW(INDIRECT("1:40")) such that:

FALSE * 1 = 0
FALSE * 2 = 0
FALSE * 3 = 0
TRUE * 4 = 4
...
TRUE * 36 = 36
...
FALSE * 40 = 0

The results of the array multiplication are then passed to the MAX function.
In the above example that would be 36 and 36 is in turn passed to the LEFT
function which means to return the first 36 characters from the string in
A11.

As written, that formula seems to have a bug in that it still returns a
trailing space. Maybe that's why "they" used TRIM but it's not working.
Maybe this instead:

=IF(LEN(A11)<=40,A11,TRIM(LEFT(A11,MAX((MID(A11,ROW(INDIRECT("1:40")),1)="
")*ROW(INDIRECT("1:40"))))))

OR:

=IF(LEN(A11)<=40,A11,LEFT(A11,MAX((MID(A11,ROW(INDIRECT("1:40")),1)="
")*ROW(INDIRECT("1:40")))-1))

Biff

 

[Aladin Akyurek]

It creates an ascending integer sequence with a step value of 1, that is...

{1;2;3;4;...;40}

 

[Max]

... just another attempt to learn by trying to explain ..

Exp1: ROW(INDIRECT("1:40")) produces an array:
{1;2;3;4;5; .... 36;37;38;39;40}

We can see the above result by carefully selecting just the expression above
in the formula bar and then press F9*
(I'd usually select the bulk of the expression with the mouse first, sweep
left-to-right, then do fine-selection towards the end by holding down the
Shift key and tapping the right-arrow key)
*CTRL+Z > Esc or click undo > Esc to revert

Exp2: MID(TRIM(A11),ROW(INDIRECT("1:40")),1)
will produce an array of each of the first 40 characters (which includes all
the single spaces in-between, if any) within the trimmed A11

Exp3: MID(TRIM(A11),ROW(INDIRECT("1:40")),1)=" "
will then produce an array of FALSE's and TRUE's, with the TRUE's
corresponding to / indicating the positions of the single spaces within A11,
viz.: MID(...) =" "
within the first 40 characters in the trimmed A11

Multiplying: Exp3 x Exp1 in turn resolves to an array of zeros and numbers
(zeros where Exp3 resolves to FALSE (FALSE = 0), and numbers where Exp3
resolves to TRUE (TRUE = 1), producing an array which might look like this:

{0;0;0;0;0;0;0;0;0;0;11;0;0;0;0;0;0;0;0;0;0;22;0;0;0;0;0;0;0;0;0;0;33;0;0;0;
0;0;0;0}

MAX(Exp3 x Exp1) then simply returns the highest figure from the array, e.g.
"33" in the example result array above

This "33" is then used in the expression:
LEFT(TRIM(A11),MAX(Exp2 x Exp1))
as the number of characters from the left
to return from the trimmed A11

So what we'd get if the trimmed A11 contains text and single spaces is a
neat extract of whatever's within the 1st 40 characters, truncated at (&
inclusive of) the last single space

Perhaps for better consistency in results, think the posted formula could be
amended a little so that it omits returning the trailing single space (at
the right end), and if the trimmed length is <= 40, return just the trimmed
A11, viz.:

=IF(LEN(TRIM(A11))<=40,TRIM(A11),LEFT(TRIM(A11),
MAX((MID(TRIM(A11),ROW(INDIRECT("1:40")),1)=" ")
*ROW(INDIRECT("1:40")))-1))

(array-entered)

--

I have a several _array_ formulas (written by someone else) that are breaking
up a long text string into smaller parts (without splitting a word in half).
I'm trying to usnerstand what the function is actually doing.
The first function (which pulls out the first 40 characters (rounded down to
the nearest full word) is the following:

=IF(LEN(A11)<=40,A11,LEFT(TRIM(A11),MAX((MID(TRIM(A11),ROW(INDIRECT("1:40"))
,1)=" ")*ROW(INDIRECT("1:40")))))

 

[Max]

Oops, typo in line:

LEFT(TRIM(A11),MAX(Exp2 x Exp1))

should read as: