• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. S

    Formula Challenge 011 - distinct/duplicate/unique array from text AND numbers

    Hi Jeff I've joined the discussion late, and haven't tried to follow why you're using OFFSET(OFFSET)... but I replicate your weird spreadsheet behaviour, and apparently can fix it by tweaking =OFFSET(OFFSET(d,,{0,1,2,3,4,5},,),,) to =OFFSET(OFFSET(d,,{0,1,2,3,4,5},,1),,)
  2. S

    Formula Challenge 016 - Unique Items in a Delimited String

    Jeff It will return 1 if you don't array-enter, otherwise 11? The last FIND() term actually simplifies to ROW(1:999) in this context, and my latest is: =COUNT(1/SIGN(FIND(S&MID(A1,FIND(S,S&A1,ROW(1:999)),FIND(S,A1&S,ROW(1:999))-FIND(S,S&A1,ROW(1:999)))&S,S&A1&S)=ROW(1:999)))
  3. S

    Formula Challenge 016 - Unique Items in a Delimited String

    {=SUM(--IFERROR(FIND(S&MID(A1,FIND(S,S&A1,ROW(1:999)),FIND(S,A1&S,ROW(1:999))-FIND(S,S&A1,ROW(1:999)))&S,S&A1&S)=FIND(S,S&A1,ROW(1:999)),0))} where S is the separator, e.g. ","
  4. S

    Formula Challenge 001 - Return everything in string after first block of numbers

    PS: Oops, sorry Jeff. I came here for the first time via a link from Hui in the Excel Hero chatroom, and thought I had a clever formula to offer. But now that I have read your summary post in Chandoo, I see I've used exactly the same logic as your original idea, i.e. find the first letter...
  5. S

    Formula Challenge 001 - Return everything in string after first block of numbers

    =MID(A1,MATCH(1,(CODE(MID(A1,ROW(A:A),1))>57)*(CODE(MID(A1,ROW(A:A)-1,1))<58),0),LEN(A1)) 89 characters
Back
Top