Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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")).

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    OK, good point!

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

  8. #8
    3 Star Lounger
    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

Posting Permissions

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