Replace word in sentence function...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Runt - 13 Apr 2004 20:16 GMT

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

Reply to this Message

ianripping - 13 Apr 2004 20:35 GMT

can you upload example, would be easier to view

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Debra Dalgleish - 13 Apr 2004 22:25 GMT

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),".....")

> I have the following function which replaces a word in a sentence with
> "....."
[quoted text clipped - 8 lines]
> Any ideas how I could capture any remaining letters at the end of the
> word and get the result "I ..... at home"?

Signature

Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply to this Message

Runt - 14 Apr 2004 09:52 GMT

The solution you gave...

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

...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

> You can search for the space at the end of the word, and replace to that
> point:
[quoted text clipped - 14 lines]
> > Any ideas how I could capture any remaining letters at the end of the
> > word and get the result "I ..... at home"?

Reply to this Message

Runt - 14 Apr 2004 10:25 GMT

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

Reply to this Message