Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2007
    Posts
    72

    Unanswered: Help with understanding formula

    Hi All,

    I have found this formula online for finding the position of the last "-" in a string in A2

    =LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="-"),ROW(INDIRECT("1:"&LEN(A1))))

    It works perfectly but I cant get my head around how this actually works. Can anyone explain how this is doing what it is doing?

    Many thanks,

    OB1

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    You can step through a function in Excel. It evaluates each section and displays the result as it goes.

    Lookup returns the specified cell value from a one-dimensional array.
    Mid parses a string and returns the specified substring.
    Row returns the row number of the specified range.
    Indirect converts a string into a range (for use within functions that take cell ranges as arguments).
    Len returns the length of a string.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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