if a cell in Excell contains a certain word, how can I take all o.

H

Hamner

if a cell in Excell contains a certain word, how can I take all of the words
to the right and place them in an adjacent cell.
Cell might contain "The Dark Tower: The Dark Tower VII by Stephen King"
I would like to take all the words to the right of "by" and put them in the
adjacent cell, while stripping the word "by" and the text following as well.
 
A

Arvi Laanemets

Hi

With text in A1
=LEFT(A1;FIND("by";A1)-2)
=RIGHT(A1,LEN(A1)-FIND("by",A1)-2)

Arvi Laanemets


Hamner said:
if a cell in Excell contains a certain word, how can I take all of the words
to the right and place them in an adjacent cell.
Cell might contain "The Dark Tower: The Dark Tower VII by Stephen King"
I would like to take all the words to the right of "by" and put them in the
adjacent cell, while stripping the word "by" and the text following as
Click to expand...
well.
 
F

Frank Kabel

Hi
try using 'Data - Text to <columns'

or for a formula trys:
=TRIM(MID(A1,FIND("by",A1)+3,1024))
 
Top