How to filter free text in EXCEL?

Question

Is there a formula to extract a word or phrases from a free text string in a cell? for exp, cell A2 contains "the dog jumped over the fence". in cell B2, i would like to extract the word "dog". However, the word dog is not always placed in the same sequence because cell A3 could say "the cat is fatter than the dog" but i still want to extract just the word "dog".

I have tried FIND() but that only works if the word im looking for is in the same place in the text string.

Answer 1

If Column A has all the sentences that contained the word "dog", and cell A1 contains the very first sentence, then your formula would look like this

=MID(A1,FINDB("dog",A1,1),3)

And just copy and paste the formula.

Answer 2

hmmm.. you might have to use in formulas combination... you can try using Search() or find()---- call the result X, with a If(), then when returning the word "dog", use the mid() function using X and Len() of the sentence as the parameter to define....

Not too sure though this is the shortest way to get what you want...

Answer 3

You'll have to nest the search function in a mid statement. Something like:

=mid(A2,search("dog",A2),3)

Answer 4

Put "dog" in A1 then in B2, put the following formula...

=MID(A2,SEARCH(A1,A2),LEN(A1))

This is just a more generic formula.

Answer 5

=IF(ISERROR(SEARCH(A1,A2)),"Not found",MID(A2,FINDB(UPPER(A1),UPPER(A2),1),LEN(A1)))

Where Cell A1 is the Search string
Cell A2 is the Text string

Answer 6

you can use mid function provided in excel.

e.g
mid(B2,5,3) will give you dog.

Answer 7

dude that makes no sense whatsoever