Replace word in sentence function...

R

Runt

I have the following function which replaces a word in a sentence with
"....."

=REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....")

Where the word is E2 and the sentence is I2.

At the moment, if the word is "work" and the sentence is "I worked at
home" I get the sentence "I .....ed at home".

Any ideas how I could capture any remaining letters at the end of the
word and get the result "I ..... at home"?

I'd greatly appreciate your help.

Thanks in advance,

Chris
 
D

Debra Dalgleish

You can search for the space at the end of the word, and replace to that
point:

=REPLACE(I2,SEARCH(E2,I2),
SEARCH(" ",I2,SEARCH(E2,I2))-SEARCH(E2,I2),".....")
 
R

Runt

The solution you gave...
=REPLACE(I2,SEARCH(E2,I2),
SEARCH(" ",I2,SEARCH(E2,I2))-SEARCH(E2,I2),"....."
Click to expand...

....is excellent except that it doesn't replace the word if it is at
the end of the sentence because of the lack of a space or the
existance of a full-stop (a.k.a period).

Is there any way around this?

Thanks,

Chris
 
R

Runt

I seem to have solve the end-of-sentence problem by substituting the
punctuation for spaces as follows:

=REPLACE(I2,SEARCH(E2,I2), SEARCH(" ",SUBSTITUTE(SUBSTITUTE(I2,".","
"),"?"," "),SEARCH(E2,I2))-SEARCH(E2,I2),".....")

But, I've stumbled across a complication which has stretched my tiny
brain to its limit.

If the text in cell (E2) has a space in it, such as "TRYING TO" and I
want to remove it from the sentence "I am TRYING TO remove this.",
then I am getting

"I ..... TO remove this." instead of

"I ..... remove this." which is what I want.

Any ideas?

Cheers,

Chris
 
Top