INDIRECT as part of Array formula – hows it work? (excel 97 on win xp sp2)

Topic

New Reply

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:

=RIGHT(TRIM(A2),LEN(TRIM(A2))-MAX(IF(MID(LEFT(TRIM(A2),20),ROW(INDIRECT("1:20")),1)=" ",
              ROW(INDIRECT("1:20")))))

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

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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”)).

Reply | Quote

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

Reply | Quote

OK, good point!

Reply | Quote

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.

Reply | Quote

Yep, see all points now.
The formula from Aladin works well and I find it easy to break down and understand.
Cheers everyone.
Alan

Reply | Quote