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,RO W(INDIRECT("1:40")),1)="
")*ROW(INDIRECT("1:40"))))))

OR:

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

Biff

"kevin frisch" wrote in message
...
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