Find a word inside a sentence and output it

Microsoft Office excel 2007 home & stude...
August 10, 2010 at 09:34:52
Specs: Windows XP
I looked for a simple way to do this with a formula but did not find the right one yet.
I have a list of words and their translation in 2 columns. For example:
House | Maison
Moon | Lune
Day | Jour
and so on.
Then I have in another sheet one column with sentences, one sentence in one cell, but may have different line breaks.
Condition: if a word in the array of source words (all in Column A in Sheet2) is contained in the text in A2 in Sheet1, I want the corresponding word in the next column appears in B2 in Sheet one (next to the sentence). If no words from the list is contained in the sentence, then output nothing.
And this was problem 1.
Now problem 2.
Same thing, but I wonder if it is possible to have in B2 not the value alone found from the list in the sentence, but! the same sentence from A2, but instead of the original word, the translated word.
This is a house.
The word House is in the dictionary, and is found in the sentence. I'd like to read "This is a Maison" in B2. Is this possible with a formula?
Thank you in advance!

See More: Find a word inside a sentence and output it

August 10, 2010 at 11:10:18
This formula should get you going on half your problem:

The text string should be in cell A1

The word your looking for in cell D1

In Cell B1 enter the formula:

=IF($D1=0," ",IF(ISERROR(FIND(LOWER($D1),LOWER(A1),1)),"No","Yes"))

Modify to suit your needs.


Report •

August 10, 2010 at 11:54:02
Hi mmcconaghy,

thank you but maybe I wasn't clear. Any of the words in a range A1:A100 in sheet2 could be contained in A1 in Sheet1. I then want to see which word from the range specified is contained in sentence in A1.
Sentence in A1 is:
This is a sentence.
In the array in sheet2 I have a term in each row in col A:
and their counterpart in col B:
I want that in B1 in Sheet1 (where I place the formula?) the output is the cell next to Sentence ie: Phrase, because it's the first and only entry contained in the sentence.

Report •

August 10, 2010 at 12:02:51
Only a macro will allow you to loop through the words in a sentence and compare them to a list of words someplace else.

A while back I wrote some code to search a sentence for a specific word and color that word red within the sentence.

If I can dig up that code, I'm sure it could be modified to do what you are asking. I'll look for it tonight when I get home. Too busy right now.

Report •
Related Solutions

Ask Question