Split text with condition in excel

[notbanme]

hi , i have excel sheet i want to split data with condition . here is ex:

i want to split text if beautiful word in mid or not in starting . if in starting as above in row 2 result would like in cell b . if "beautiful" in mid or any other position then result would like row 1 and row 3 in cell b and cell c . hope you understand my question. thanks

[Special-K]

Try

for the left bit
=LEFT(A1,SEARCH("beautiful",A1)-1)

for the right bit
=SUBSTITUTE(A10,LEFT(A10,SEARCH("beautiful",A10)-1),"")

[Special-K]

Try

for the left bit
=LEFT(A1,SEARCH("beautiful",A1)-1)

for the right bit
=SUBSTITUTE(A10,LEFT(A10,SEARCH("beautiful",A10)-1),"")

[notbanme]

thanks its work

[shukla.ankur281190]

Try

B1=IF(SEARCH("beautiful",$A1)<=1,$A1,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",50)),(COLUMNS($B1:B1)-1)*100+1,100)))