Replace word in sentence function...

  • Thread starter Thread starter Runt
  • Start date Start date
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
 
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),".....")
 
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
 
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
 
Back
Top