Split Text Using Criteria

[Kenshin]

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



thanks

 

[bosco_yip]

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

 

[Hareesh_KLD]

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

 

[NARAYANK991]

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

 

[bosco_yip]

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