Home |
Search |
Today's Posts |
#1
|
|||
|
|||
What is ROW(INDIRECT("1:40")) doing? (array formula)
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
It creates an ascending integer sequence with a step value of 1, that is...
{1;2;3;4;...;40} kevin frisch wrote: 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 |
#4
|
|||
|
|||
... 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) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "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 |
#5
|
|||
|
|||
Oops, typo in line:
LEFT(TRIM(A11),MAX(Exp2 x Exp1)) should read as: LEFT(TRIM(A11),MAX(Exp3 x Exp1)) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel array formulas | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Where is the bug in my array? | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |