# Thread: INDIRECT as part of Array formula - hows it work? (excel 97 on win xp sp2)

1. ## 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

2. ## 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>

3. ## 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.

4. ## 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")).

5. ## 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

6. ## Re: INDIRECT as part of Array formula - hows it work? (excel 97 on win xp

OK, good point!

7. ## 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.

8. ## 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•