How to separate Certain INFO from a Column

[Gmata]

I have a bunch of products and i need to separate the brand of the products
For example:
Grey Flannel Cologne by Geoffrey Beene for Men
Lady Stetson Perfume by Coty for Women
Envy Cologne by Gucci for Men
Gucci Rush 2 Perfume by Gucci for Women
Coty Musk Cologne by Coty for Men
Tabu Perfume by Dana for Women
Canoe Cologne by Dana for Men

The brand is between by and for

Grey Flannel Cologne by "Geoffrey Beene" for Men

Thanks

 

[D.]

Try
=IF(ISERROR(FIND("by",A1)),"",LEFT(A1,FIND("for",A1)-1))

 

[Max]

One crack ..

Assuming data running in A1 down
In B1:
=TRIM(MID(A1,SEARCH("by",A1)+2,SEARCH("for",A1)-SEARCH("by",A1)-2))
Copy down to extract the "brand"

 

[excelent]

Try search and replace function :
(if u still ned text other than the brand,- do it on a copy column)

search for : *by[1 space]
replace with : nothing
then
search for : [1 space]for*
replace with : nothing


"Max" skrev:

 

[Bernard Liengme]

=MID(A1,FIND("by",A1)+3,FIND("for",A1)-FIND("by",A1)-4)
best wishes