Return all, after/before certain word.

[gibby3164]

I can use this function:

=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

to remove all but the first word in a string, but need to be able to broaden the range.



For example, say I have the following text in A1:

"This is the text I need to edit, with the word slinky being the common value."

I need to be able to take a common value in a text area (in this case that common being "slinky") and remove everything before that word as well as the word itself. I also need to be able to do the same thing, and have a formula to remove everything AFTER a common value.

"being the common value."

or

"This is the text I need to edit, with the word"


Not sure if its possible to also setup a formula to eliminate text between 2 common values? For instance, being able to remove everything between "[" and "]" in the following:

"This is the text I [need to edit, with the word] slinky being the common value."

Which would return:

"This is the text I slinky being the common value."


Thanks in advance for any assistance!!!

[daddylonglegs]

If text to find, e.g. "slinky" is in B1 try this formua for all before

=TRIM(LEFT(A1,SEARCH(B1,A1)-1))

and for after

=TRIM(REPLACE(A1,1,SEARCH(B1,A1)-1+LEN(B1),""))

[gibby3164]

Thanks daddylonglegs!

That is a perfect solution for the first two examples. Do you have any idea if the last example with a range is possible?

Thanks again!

[Richard Schollar]

Wouldn't you just join the results of the formulas given for that like:

=TRIM(LEFT(A1,SEARCH(B1,A1)-1) & REPLACE(A1,1,SEARCH(B1,A1)-1+LEN(B1),""))

Richard

[gibby3164]

Absolutely. I guess I should think before I talk!

Thanks for the assistance.