How can I replace a word from a sentence in a cell & substitute word from another cell

**rionoah** · 09-11-2012, 07:55 AM

How can I replace a word from a sentence in a cell and substitute the word from another cell.
example:

A1 > apple
A2 > banana
A3 > carrot
.
.
A100 > grape

B1 > I ate food this morning (using ONLY cell B1 as source of sentence)

**this is the OUTPUT after replacing word "food"
C1 > I ate apple this morning
C2 > I ate banana this morning
C3 > I ate carrot this morning
.
.
C100 > I ate grape this morning

**rionoah** · 09-11-2012, 07:59 AM

replace "food" with value from cell A

**DannyJ** · 09-11-2012, 08:16 AM

Hi,

Try

="I ate "&A1&" this morning"

Then of course just drag the formula down as far as it needs to go.

Hope this helps,

Danny

**rionoah** · 09-11-2012, 08:39 AM

Thank you very much Danny for your help.

**rionoah** · 09-14-2012, 01:25 AM

Thanyou for your help.

**nested.if1@gmail.com** · 09-11-2012, 08:22 AM

Yes...DannyJ is abslutely right.
However in case if you want to use the formula only then below might help

=SUBSTITUTE($B$1,"food",$A1)

**DannyJ** · 09-11-2012, 08:36 AM

Thanks Nested,

Your way would probably work smoother overall. Just to make it clearer for rionoah, B1 has I ate food this morning and C1 is the word you want to substitute in. I know it's obvious, you can never make something too easy!

Regards

Danny

**DannyJ** · 09-11-2012, 08:44 AM

No worries.

I would suggest using Nested's way of doing it though as it is much simpler. If you want an example of how to do it that way I will be happy to knock one up.

If your question is answered please make to sure to mark the topic solved.

Thanks

Danny

How can I replace a word from a sentence in a cell & substitute word from another cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How can I replace a word from a sentence in a cell & substitute word from another cell

Re: How can I replace a word from a sentence in a cell & substitute word from another cell

Re: How can I replace a word from a sentence in a cell & substitute word from another cell

Re: How can I replace a word from a sentence in a cell & substitute word from another cell

Re: How can I replace a word from a sentence in a cell & substitute word from another cell

Re: How can I replace a word from a sentence in a cell & substitute word from another cell

Re: How can I replace a word from a sentence in a cell & substitute word from another cell

Re: How can I replace a word from a sentence in a cell & substitute word from another cell

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions