1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Split Text Using Criteria

Discussion in 'Ask an Excel Question' started by Kenshin, Mar 12, 2018.

  1. Kenshin

    Kenshin Member

    Messages:
    76
    Have a nice days chandoo.....i desperate have question, please see my file attach



    thanks

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,905
    Maybe,

    1] Select criteria dropdown list in B3

    2] In B7 :

    =LEFT($A$1,FIND("@",SUBSTITUTE($A$1," ","@",$B$3))-1)

    3] In B8, copied down :

    =IFERROR(MID($A$1,FIND("@",SUBSTITUTE($A$1," ","@",$B$3*ROWS($1:1)))+1,MMULT(FIND("@",SUBSTITUTE($A$1," ","@",TRANSPOSE(MMULT(ROW($A1:$A2),1))*$B$3)),{-1;1})-1),"")

    Regards
    Bosco

    Attached Files:

    Last edited: Mar 12, 2018
  3. Hareesh_KLD

    Hareesh_KLD Member

    Messages:
    73
    Hi, tried it in other way, results same as Bosco, but with additional space in the begining from B8

    B7:

    =LEFT(A1,FIND("@",SUBSTITUTE(A1," ","@",B3)))

    B8: (copy to down)
    =IFERROR(MID($A$1,SUMPRODUCT(LEN($B$7:B8)),FIND("@",SUBSTITUTE($A$1," ","@",$B$3*(ROW()-6)))-SUMPRODUCT(LEN($B$7:B8))),"")

    Regards,
    Hareesh

    Attached Files:

    Khalid NGO and NARAYANK991 like this.
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Both formulae need to have a minor tweak so that the last couple of words are not missed in the last line :

    =IFERROR(MID($A$1 & REPT(" ", $B$3 - 1),FIND("@",SUBSTITUTE($A$1 & REPT(" ", $B$3 - 1)," ","@",$B$3*ROWS($1:1)))+1,MMULT(FIND("@",SUBSTITUTE($A$1 & REPT(" ", $B$3 - 1)," ","@",TRANSPOSE(MMULT(ROW($A1:$A2),1))*$B$3)),{-1;1})-1),"")

    Narayan
  5. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,905
    In respect to Narayan's advised to return the last couple of words, here's my fixed formula for just a single formula and shorter.

    1] Select criteria dropdown list in B3

    2] In B7, copied down :

    =TRIM(MID(SUBSTITUTE(" "&A$1," ",REPT(" ",300)),(B$3*ROWS($1:1)-B$3+1)*300,B$3*300))

    Remark :

    Difference between this formula and Narayan's modified formula :

    In the "last line of words", Narayan's modified formula will have trailing spaces and my formula don't have.

    Regards
    Bosco

    Attached Files:

    Last edited: Mar 13, 2018

Share This Page