Results 1 to 8 of 8

20060616, 09:27 #1
 Join Date
 Apr 2001
 Location
 Warrington, Cheshire
 Posts
 355
 Thanks
 3
 Thanked 0 Times in 0 Posts
INDIRECT as part of Array formula  hows it work? (excel 97 on win xp sp2)
Just a quick query really.
I have been passed an array formula which basically returns the final word in a cell of text, as indicated by the final word being preceded by a space.
The formula is:
<pre>=RIGHT(TRIM(A2),LEN(TRIM(A2))MAX(IF(MID(LEFT(TRIM(A2),20),ROW(INDIRECT("1:20")) ,1)=" ",
ROW(INDIRECT("1:20")))))</pre>
entered as an array formula.
Basically the formula returns a persons surname.
I am aware of using indirect to return indirect cell values, but how does the above formula work? Just having difficulty getting my brain round the ROW(INDIRECT("1:20")) bit.
Is it checking characters 1:20 of the cell? And how does it know its the last space in a cell. e.g. if I have say Mr K L Smith, then the result is Smith. It's great and it works but I would just like to know how.
TIA
Alan

20060616, 10:11 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: INDIRECT as part of Array formula  hows it work? (excel 97 on win xp
ROW($1:$20) or ROW(INDIRECT("1:20")) returns an array consisting of the numbers 1, 2, 3, ..., 20.
LEFT(TRIM(A2),20) consists of the first 20 characters (or as many as available) of A2.
MID(LEFT(TRIM(A2),20),ROW(INDIRECT("1:20")),1) returns an array consisting of the 1st, 2nd, ... character of A2. For example, if A2 = "Mr K L Smith", the result is
"M", "r", " ", "K", " ", "L", " ", "S", "m", "i", "t", "h", "", "", "", "", "", "", "", ""
Next, we compare these to a space " ", and return the highest number from the array 1, 2, ..., 20 for which there is a match, i.e. the position of the last space. This is subtracted from the length of the value of A2, and the result is used to return the last n characters of the value of A2.
Clear as mud now? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

20060616, 12:01 #3
 Join Date
 Apr 2001
 Location
 Warrington, Cheshire
 Posts
 355
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: INDIRECT as part of Array formula  hows it work? (excel 97 on win xp
Cheers Hans, as clear as most things to come out of Redmond.
The thing I most couildn't visualise was the need for the ROW in front of indirect 1:20.
Is that because without it the indirect would not return the actual row number of the data held in the array  I'm guessing.

20060616, 12:10 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: INDIRECT as part of Array formula  hows it work? (excel 97 on win xp
1:20 is a cell reference  it refers to rows 1 through 20. ROW(A5) returns the row number of the argument (5 in this example), and ROW(1:20) returns an array containing the row numbers 1, 2, ..., 20.
As far as I can tell, INDIRECT is not really needed here, ROW(1:20) works just as well as ROW(INDIRECT("1:20")).

20060616, 13:30 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: INDIRECT as part of Array formula  hows it work? (excel 97 on win xp
The indirect is used for generality. If rows are inserted/deleted within 1:20 the formula will change and expand/contract.
Using Indirect "locks" it as 1:20 no matter what rows are inserted/deleted within the range.
Steve

20060616, 13:33 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: INDIRECT as part of Array formula  hows it work? (excel 97 on win xp
OK, good point!

20060618, 21:28 #7
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: INDIRECT as part of Array formula  hows it work? (excel 97 on win xp
Now you know how the ROW(INDIRECT(...)) bit works, isn't that a too expensive formula?
An alternative would be:
=TRIM(RIGHT(" "&A2,LEN(" "&A2)FIND("@",SUBSTITUTE(" "&A2," ","@",LEN(" "&A2)LEN(SUBSTITUTE(" "&A2," ",""))))))
which just needs enter. BTW, the string in A2 should not have trailing spaces.Microsoft MVP  Excel

20060619, 06:35 #8
 Join Date
 Apr 2001
 Location
 Warrington, Cheshire
 Posts
 355
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: INDIRECT as part of Array formula  hows it work? (excel 97 on win xp
Yep, see all points now.
The formula from Aladin works well and I find it easy to break down and understand.
Cheers everyone.
Alan